Is there an Excel function that returns the body of another function?

2

Simple example of what I want: suppose that in cell A1 I have the value 2, in cell A2 I have the value 3 and in cell A3 I have the formula = A1 + A2.

If I put the formula = A3 in cell A4, it will obviously return the value 5 and what I would like is a formula that would return the string '= A1 + A2'. Is there a formula?

Thanks in advance.

    
asked by anonymous 11.09.2018 / 18:02

2 answers

2

A User Defined Function (UDF) can be created for Excel 2010 or earlier versions:

Function MostraFormula(Rng As Range, Optional asR1C1 As Boolean = False) As String
    If asR1C1 Then
        MostraFormula = Rng.FormulaR1C1
    Else
        MostraFormula = Rng.FormulaLocal
    End If
End Function

Then inserting the following example data:

      A   B        C                 D           
 --- --- --- -------------- -------------------- 
  1   2   3   =SOMA(A1;B1)   =MostraFormula(C1)  
  2                                              
  3   

The result is:

      A   B   C        D        
 --- --- --- --- -------------- 
  1   2   3   5   =SOMA(A1,B1)  
  2                             
  3                             

Or with =MostraFormula(C1; VERDADEIRO) the result is =SUM(RC[-2],RC[-1])

    
11.09.2018 / 19:21
5

You can use FÓRMULATEXTO()

Example:

Being:

A1=10

B1=20

C1==A1+B1

E1==FÓRMULATEXTO(C1)

Complementing

Asimpleexampleusingthefunctiontobuildacalculationmemory:

Ifyouusethenamedrangesfeature,youcanmakeitlookevenbetter...

Reference: Function Tip - FORMULATEXTO

    
11.09.2018 / 18:16