1

is it possible to do formula substitution easily in excel?

I have a worksheet similar to below example (albeit more complex, hence the need for some function in excel if there exists one)

What I want to do is replace the total sales function with the actual input values, rather than formulas.

F6+I6+L6+O6 = F9 + L9
F4+F5+I4+I5+L4+L5+O4+O5 = F6+I6+L6+O6

that is 
F9 + L9 becomes F4+F5+I4+I5+L4+L5+O4+O5

enter image description here

Hope you understand what I'm after

  • Try over at webapps.stackexchange.com – aparente001 Aug 6 '15 at 13:07
  • 1
    It is not an webapp. And afaik, excel questions are on topic – Viktor Mellgren Aug 6 '15 at 13:13
  • Oh, right. I get confused, Excel goes on one site, Google Sheets goes on the other. – aparente001 Aug 6 '15 at 13:16
  • You want your formulas to reference all precedents rather than how you typed them? – Raystafarian Aug 6 '15 at 13:18
  • @Raystafarian exactly, to the root precedents if possible, but one step down is also okay, if i can do it repeatedly – Viktor Mellgren Aug 6 '15 at 13:19
2

I wrote a quick code which should do what you want.

I'm it basically checks if a cell contains a formula and if it does it replaces all instances where that cell is referenced with that formula.

Once its gone through the sheet if it found any replacements it loops again (not actually sure it needs this but was easier to put in). I also have no idea how fast this will run on complex spreadsheets.

Note it treats $A$1,$A1,A$1, and A1 as the same it doesn't have anyway of determining if there should be some frozen cells in the reference.

Sub replace_formulas()
Dim cell_count As Long, flag As Boolean

Do

flag = False

For Each c In ActiveSheet.UsedRange
    If c.HasFormula Then

        'count number of replacements
        cell_count = Application.WorksheetFunction.CountIf(Cells, c.Address) + _
            Application.WorksheetFunction.CountIf(Cells, Replace(c.Address, "$", "")) + _
            Application.WorksheetFunction.CountIf(Cells, Replace(c.Address, "$", "", 1, 1)) + _
            Application.WorksheetFunction.CountIf(Cells, "$" & Replace(c.Address, "$", ""))

        'If there is at least one replacement loop through all the cells after this one
        If cell_count > 0 Then flag = True

        'Replace cell references with and without $ ($A$1,$A1,A$1,A1)
        Cells.Replace What:=c.Address, Replacement:="c.formula", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:=Replace(c.Address, "$", ""), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:=Replace(c.Address, "$", "", 1, 1), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        Cells.Replace What:="$" & Replace(c.Address, "$", ""), Replacement:=Right(c.Formula, Len(c.Formula) - 1), LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    End If
Next

Loop While flag = True

End Sub
1

You may have to do it manually, but there is a feature to assist you: Trace precedents (in Formulas tab in Excel 2013).

This will add arrows showing where the formula you have selected originates, so if you selected the total sales in column J in your example and clicked trace precedents then it would draw arrows from F9 and L9 to it.

  • I know of that functionality, but i have a large spreadsheet that will be about 600 cells in the final formula, (and with maybe 15 level deep formulas) so manual work is not feasible, hence the question :) – Viktor Mellgren Aug 23 '15 at 9:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.