Can child cells in spreadsheet use a master formula in a parent cell at the top?

[Viper88]

Is there a way for child cells in a spreadsheet to use the formula in a parent cell at the top of a spreadsheet? The object being that if the parent formula is changed, all of the child formulas are also changed.

To illustrate, assume I create a parent formula in cell E1. This formula might not be 100% correct and so may have to be changed later.

In the body of the spreadsheet, I have child cells that reference the formula in parent cell E1. Each child formula uses values on it own row for its calculations. So my spreadsheet looks something like this:

Row
01 .. Values in columns B1, C1, D1. (E1 = Parent formula) <== Master Formula
02
03 .. Calculate some values not related to the formula.
04 .. Values in columns B4, C4, D4. (E4 = reference to parent formula)
05
06 .. Calculate some values not related to the formula
07 .. Values in columns B7, C7, D7. (E7 = reference to parent formula)

The parent (Master) formula in cell E1 uses the dummy values in cells B1, C1 & D1.
The child formula in E4 uses the values in B4, C4 & D4 for its calculations.
The child formula in E7 uses the values in B7, C7 & D7 for its calculations.

So if I change the Parent Formula, the formulas in E4 and E7 also change. This negates having to change the formula in all of the child cells in the spreadsheet manually.

WHAT I'VE TRIED:
If I copy the formula in the parent cell to all of the child cells, everything works as expected.
But if I type "E1" in the child cells, the child cells just show the "result" of the calculation in cell E1. I don't want the result of E1, I want the formula in E1.
I've also tried naming cell E1 as "MasterFormula" and using the name in the child cells. Same thing, the child cells show the "result" of E1, not the "formula" in E1.

I'm using Excel 2000 on a Windows 7 PC.

Thanks,
Viper88

[Bob Phillips]

Maybe you could use the UDF

Function ApplyFormula(ByRef cell As Range) As VariantDim sf1 As String


    sf1 = Replace(cell.Formula, "ROW()", Application.Caller.Row)
    sf1 = Replace(sf1, "COLUMN()", Application.Caller.Column)
    sf1 = Application.ConvertFormula(sf1, xlA1, xlR1C1, , cell)
    sf1 = Application.ConvertFormula(sf1, xlR1C1, xlA1)
    ApplyFormula= Application.Caller.Parent.Evaluate(sf1)
End Function

And use in E4 like so, =ApplyFormula($E$1)

[Viper88]

Maybe you could use the UDF

Function ApplyFormula(ByRef cell As Range) As VariantDim sf1 As String


    sf1 = Replace(cell.Formula, "ROW()", Application.Caller.Row)
    sf1 = Replace(sf1, "COLUMN()", Application.Caller.Column)
    sf1 = Application.ConvertFormula(sf1, xlA1, xlR1C1, , cell)
    sf1 = Application.ConvertFormula(sf1, xlR1C1, xlA1)
    ApplyFormula= Application.Caller.Parent.Evaluate(sf1)
End Function

And use in E4 like so, =ApplyFormula($E$1)

Hi Bob,

Thanks for helping out. I had to Google UDF and learned that it stands for "User Defined Function" and that it uses a Visual Basic script. (hope that's right)
Learned how to create a VB script from youtube.

However, when I type the first line of your solution:

Function ApplyFormula(ByRef cell As Range) As VariantDim sf1 As String

When I press ENTER, I get an error popup that says: "Compile Error, Expected: End of Statement" and "sf1" is highlighted.

What am I doing wrong?

Thanks,
Viper88

[Viper88]

Hi Bob,

Thanks for helping out. I had to Google UDF and learned that it stands for "User Defined Function" and that it uses a Visual Basic script. (hope that's right)
Learned how to create a VB script from youtube.

I open VB and do a "Insert Module" and the "Book1 - Module1 (code)" window opens.

However, when I type the first line of your solution:

Function ApplyFormula(ByRef cell As Range) As VariantDim sf1 As String

When I press ENTER, I get an error popup that says: "Compile Error, Expected: End of Statement" and "sf1" is highlighted.

What am I doing wrong?

Thanks,
Viper88

Added the line in red to clarify what I did.

[Bob Phillips]

Sorry, the forum has a habit of merging the first two lines. Try this

Function ApplyFormula(ByRef cell As Range) As Variant
Dim sf1 As String

    sf1 = Replace(cell.Formula, "ROW()", Application.Caller.Row)
    sf1 = Replace(sf1, "COLUMN()", Application.Caller.Column)
    sf1 = Application.ConvertFormula(sf1, xlA1, xlR1C1, , cell)
    sf1 = Application.ConvertFormula(sf1, xlR1C1, xlA1)
    ApplyFormula= Application.Caller.Parent.Evaluate(sf1)
End Function

[Viper88]

Sorry, the forum has a habit of merging the first two lines. Try this

Function ApplyFormula(ByRef cell As Range) As Variant
Dim sf1 As String

    sf1 = Replace(cell.Formula, "ROW()", Application.Caller.Row)
    sf1 = Replace(sf1, "COLUMN()", Application.Caller.Column)
    sf1 = Application.ConvertFormula(sf1, xlA1, xlR1C1, , cell)
    sf1 = Application.ConvertFormula(sf1, xlR1C1, xlA1)
    ApplyFormula= Application.Caller.Parent.Evaluate(sf1)
End Function

Hi Bob,

Your solution works great now!

Wow, never would have thought that Excel didn't have your UDF as a built-in function. Seems that having a user created parent formula that is used throughout a spreadsheet would be something that Excel should handle out of the box.

You should make your solution a sticky for everyone's use. If you do that, maybe also explain step-by-step how to open Excel's Visual Basic and create the new module. IOW, the entire procedure to accomplish the goal.

I'll be studying your solution for a long time, to understand what each line does. BTW, I never saved module1. Does Excel automatically save VB modules so the user never has to worry about doing it?

Thank you very much!
Viper88

[Bob Phillips]

BTW, I never saved module1. Does Excel automatically save VB modules so the user never has to worry about doing it?

It does, if you save it as an xlsm, but that means the UDF will only be availabel to that workbook. To make it generally available, you need to build an addin.

[Viper88]

It does, if you save it as an xlsm, but that means the UDF will only be availabel to that workbook. To make it generally available, you need to build an addin.

I've bookmarked this thread. I'll just refer to it when I need to do the parent/child formula thing in future spreadsheets.

Thanks and have a great day,
Viper88