3

Say cell A1 has the formula A2 + A3

and B1 has the formula B2 + B2

and C1 has the formula A1 + B1

I need an easy and quick way to create the formula for C1 in the flattened state..

i.e. (A2 + A3) + (B1 + B2)

Although the above example is trivial, I am trying to simplify a formula that refers to around 40 cells (and around 70 calls through a third party plugin).

Any help appreciated!

2
  • What you are asking is legitimate, but functionally it isn't ideal. Complex calculations are very difficult to troubleshoot. It is best practice to show your calculation, even if the details of the calculation are ultimately hidden from the end user. Oct 31, 2013 at 20:29
  • A bit late, but shouldn't that be (A2 + A3) + (B2 + B2)?
    – Arjan
    Jul 25, 2014 at 13:45

2 Answers 2

0

If all you need is to see the formula in the C1 cell you could try this:

= CONCATENATE("= (";SUBSTITUTE(FORMULATEXT(A1);"=";"");") + (";SUBSTITUTE(FORMULATEXT(B1);"=";"");")

It does work on Excel-2013 but I couldn't test on Excel-2007.

If you need to execute that formula, you might want to check this answer on how to convert that string to a real formula:

https://stackoverflow.com/questions/4471884/turn-a-string-formula-into-a-real-formula

2
  • 1
    FORMULATEXT function is only available in Excel 2013 Oct 24, 2013 at 10:05
  • Thanks - I also would like to automatically find the precedents and build up the formula that's actually executed
    – sjhuk
    Oct 24, 2013 at 10:29
0

If you don't have Excel 2013, you can fake the FORMULATEXT function with a short function in Visual Basic. I did use the question that douglaslps referenced to return it as a value instead of just the string. (Very neat answer, that one.) In cell C1, you'd use the formula:

=CombineFormulas(A1:B1,"+")

and you'd put this function in the workbook's modules somewhere:

Option Explicit
Public Function CombineFormulas(RangeToCombine As Range, Operator As String)
    'Input:  Range of cells and an operator to use when combining their formulas
    'Output: String of the combined formulas
    Dim cell As Range
    Dim s As String
    s = "="
    For Each cell In RangeToCombine.Cells
        If Len(s) > 1 Then s = s & Operator
        s = s & "(" & Mid(cell.Formula, 2, Len(cell.Formula)) & ")"
    Next
    Application.Volatile
    CombineFormulas = Evaluate(s)
End Function

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .