Automatically combining formula of multiple cells into one cell

Question

I have an Excel sheet that has uses a lot of cells to calculate the final result. I see that those intermediate results might have been helpful when the formula was created. I'd now like to get rid of the intermediate results and only have a single formula in one cell.

I know how to do that manually, e.g. as described in this question and answer. However, I'd like to know if there's an automatic way to do that.

It seems like Excel already knows where the values are coming from when I use the trace function:

Usually I would simply hide rows to make them invisible. In this case, I cannot do it because there are values in other columns that are important.

Answer 1

The answer of Charl Pretorius was helpful. After fixing 2 issues, the Macro worked for me. I have added comments where I changed the script.

Note that the Macro needs to be run as often as you want replacements to be made. It would be possible to perform all replacements, but maybe the last reference to a cell should remain.

Sub CombineFormula()

    Dim FormulaCell As Range
    Set FormulaCell = ActiveCell

    Dim StrTemp As String
    StrTemp = FormulaCell.Formula

    'Do not replace the $ sign in absolute addresses.
    'It may be used in text, e.g. in a formula like ="US$"&A1
    'Instead, use different adressing modes in the replacement loop
    '! StrTemp = Replace(StrTemp, "$", "")

    Dim RangeRef As Range

    On Error Resume Next

    Dim ReplaceBy As String
    For Each RangeRef In FormulaCell.Precedents.Cells
        'Add in parentheses to maintain correct order of evaluation, e.g. in cases of addition before multiplication
        ReplaceBy = "(" + Replace(RangeRef.Formula, "=", "") + ")"
        'The order of the following is important
        'Replace absolute ranges first, because A$1 is also contained in $A$1
        StrTemp = Replace(StrTemp, RangeRef.Address(True, True), ReplaceBy)
        StrTemp = Replace(StrTemp, RangeRef.Address(True, False), ReplaceBy)
        StrTemp = Replace(StrTemp, RangeRef.Address(False, False), ReplaceBy)
        StrTemp = Replace(StrTemp, RangeRef.Address(False, True), ReplaceBy)
    Next

    FormulaCell.Value2 = StrTemp
End Sub

Answer 2

I'm not sure if you're happy to use macros in your worksheets, but below is a simple macro to achieve what you want. Just copy the code to your VBA, select the cell you want to clean up and run the macro, you can even create a shortcut key for it if you have a lot to do. Just be careful, if you run a macro all of your undo history will be deleted.

Sub CombineFormula()

Dim FormulaCell As Range
Set FormulaCell = ActiveCell

Dim StrTemp As String
StrTemp = FormulaCell.Formula
StrTemp = Replace(StrTemp, "$", "")


Dim RangeRef As Range

On Error Resume Next

For Each RangeRef In FormulaCell.Precedents.Cells
    StrTemp = Replace(StrTemp, RangeRef.Address(False, False), Replace(RangeRef.Formula, "=", ""))
Next


FormulaCell.Value2 = StrTemp

End Sub

Answer 3

There is no direct way to approach this as from what I know , but I think the function FORMULATEXT might come in handy for you. the function FORMULATEXT(cell number) will return you the formula as text which can be used for some modification like

A3 = FORMULATEXT(A1)+FORMULATEXT(A2) or something of that sort to achieve your goal.