How can I substitute a cell reference for the formula the referred cell contains

Question

How can I substitute a cell reference for the formula it contains, in other words, "expand" or "derivate" cell references?

An example, and I know I could calculate it using PV(): Suppose I want to calculate the present value of a given amount, reductor, number of periods and discount rate and in a spreadsheet I have:

A2: 1 (number of periods)
B2: 5000 (amount)
C2: 0,8 (reductor)
G1: 6% (discount rate)

If I want to calculate the final result on D2, I would have to enter:

=(B2*C2)*(1+$G$1)^(-A2)

(I intentionally used some unnecessary parentheses above)

But if I wanted, for debugging, or for building a more complex formula with more nested calculations write on cells:

D2: =E2*F2^G2
E2: =B2*C2
F2: =1+$G$1
G2: =-A2

So that I could check every part of the calculation is working ok and that the final formula is well "assembled" (or to easily correct what might be wrong or change it to calculate something else, like future value, for which I would remove the minus sign on G2).

And after doing those steps use some function/shortcut/feature on cell D2 that would replace

"=E2*F2^G2"

for

"=(B2*C2)*(1+$G$1)^(-A2)"

(i.e. do E2 → (B2*C2) F2 → (1+$G$1) and G2 → (-A2)) so that the desired formula is built on the right place and I can get rid of the temporary cells.

The closest to this behaviour I could find was formulatext() function, but it works just for a single reference and always include the "=" if I do, for instance

=CONCAT(FORMULATEXT(E2);"*";FORMULATEXT(F2);"^";FORMULATEXT(G2))

results in

=B2*C2*=1+$G$1^=-A2

which is not the desired result.

What I was expecting to find was something like when one select a part of a formula and presses F9 and it substitutes it for the value, but applied for functions or intermediate steps.

Excel doesn't have in-built functionality to do this. It's theoretically doable with VBA, but not a trivial task. — BigBen, Apr 24 '20 at 13:27
Isn't formulatext() already giving you 90% of the answer? You just need to get rid of all the =. Substitute function, perhaps? Or you want it to automatically cut the entire formula into pieces, then write it out? Also you might also use "evalutate formula" to analyse the formula. — Evil Blue Monkey, Apr 24 '20 at 14:40

score 0 · Accepted Answer · answered Apr 27 '20 at 00:25

As it really does not seem to exist a built-in funcion on Excel, I came out with a script for doing this based on the answer on Parsing and extracting cell references from Excel formulas?

Works on Excel 365 (may work on other versions as well), replaces references on active cell only, does not work on cells that contain intervals (for instance, it will fail on a cell that contains =sum(A1:A5) ) and the contents of the precedent cells will end up enclosed in parentheses. It also does not replace "locked" cells (=$B$2 won't be replaced as well).

In summa, it is not perfect, maybe it's not ellegant too, but it seems to be as good as I needed and works on the proposed scope.

Sub ReplacePrecedents()
Dim r As Range, rr As Range
With ActiveCell.Range("A1")
    ' store the contents of the cell
    parsedcontents = .Formula
    Set r = .DirectPrecedents
    ' iterate throughout all precedents
    For Each rr In r
        ' store each one between parentheses
        appendstr = "("
        ' check whether first character is a "=" or a value
        If StrComp(Left(rr.Range("A1").Formula, 1), "=") = 0 Then
            appendstr = appendstr & Right(rr.Range("A1").Formula, Len(rr.Range("A1").Formula) - 1)
        Else
            appendstr = appendstr & rr.Range("A1").Formula
        End If
        appendstr = appendstr & ")"
        ' do the magic
        parsedcontents = Replace(parsedcontents, rr.Address(0, 0), appendstr)
    Next rr
    ' write the parsed string to the cell
    .Formula = parsedcontents
End With
End Sub

Thank you for everyone that replied, I guess I still do not have privileges enough to upvote a comment, as soon as I do, I will.

How can I substitute a cell reference for the formula the referred cell contains

1 Answers1