Results 1 to 8 of 8

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Last edited by Viper88; 2014-08-19 at 08:44 AM.

  2. #2
    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)
    Last edited by Bob Phillips; 2014-08-19 at 02:07 PM.

  3. #3
    Quote Originally Posted by Bob Phillips View Post
    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)
    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

  4. #4
    Quote Originally Posted by Viper88 View Post
    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.

  5. #5
    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

  6. #6
    Quote Originally Posted by Bob Phillips View Post
    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
    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
    Last edited by Viper88; 2014-08-20 at 09:28 PM.

  7. #7
    Quote Originally Posted by Viper88 View Post
    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.

  8. #8
    Quote Originally Posted by Bob Phillips View Post
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •