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

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

Maybe you could use the UDF


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

Bob Phillips said:

Maybe you could use the UDF

Code:

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)

Click to expand...

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 said:

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

Click to expand...

Added the line in red to clarify what I did.

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

Bob Phillips said:

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

Code:

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

Click to expand...

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

Viper88 said:

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

Click to expand...

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.

Bob Phillips said:

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.

Click to expand...

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