Can I create a 'MASTER' formula for use throughout a workbook?

[lisamcravey]

I have (what I think is a!) pretty complicated spreadsheet of formulas, which I've saved as a blank spreadsheet to use repeatedly for different datasets.

The spreadsheet has identical formulas filling 3 of every 4 rows, and references different data depending on the row number. The other 1 of every 4 rows also share a formula, but I need them set up grouped as a 4, so it basically follows the format below:

ROW1: formula 1
ROW2: formula 2
ROW3: formula 2
ROW4: formula 2
ROW5: formula 1
ROW6: formula 2
ROW7: formula 2
ROW8: formula 2

....etc.

My issue is, I do change the formula on the blank sheet quite a bit, as I update my processes and refine workflows / output etc, and because of the way the sheet is set up, I can't copy paste the formulas throughout the workbook without individually skipping the odd row that doesn't follow the same formula.

SO, my thought was, I could have two 'master' cells at the top of the sheet which contain the main formulas, and then each cell could reference the formula in those to calculate?

i.e. Cell A1 might read "=B2+C2+D2" as a master formula, which is easily changed, and then cell E2='FORMULAofA1'+1, so E1 would use a formula in cell A1 as part of it's calculation. This means if I wanted to add A2 to the formula, I could just change cell A1 to "=A2+B2+C2+D2" and everything else would update?

This would mean if I wanted to change the formulas in my sheet I would only have to amend it in a couple of cells rather than getting into a fankle with the full sheet.

Is this possible? And if so, can anyone help with how to achieve this?

I'm using Microsoft Excel 2010.

Thanks,

Lisa

 

[mikerickson]

You example is flawed.
A1 contains a number
B1 contains a string A1+1

If C1 contains =DoSame(B1) it would be trying to add 1 to a string and an error results.

Try using this variation of DoSame

Function DoSame(theCell As Range) As Variant
    Dim Rform As String
    Application.Volatile
    If theCell.Cells.Count <> 1 Then
        DoSame = "Input must be a single cell."
    Else
        Rform = Application.ConvertFormula(theCell.FormulaR1C1, xlA1, xlR1C1, relativeto:=theCell)
        DoSame = Evaluate(Application.ConvertFormula(Rform, xlR1C1, xlA1, toabsolute:=True))
    End If
End Function

put 100 in A1
A1 + 1 in B1

And then put =DOSAME($B$1) in B2 (note that the absolute referencing is required)

as you change A2, B2 will change accordingly. Similarly if the formula is dragged down.

If B2 is dragged right, that will not work as expected. I suspect that the order in which cells are calculated is related to when the

 

[c0barnes]

no no, B1 contains a formula "=A1+1".

 

[mikerickson]

I think the recusive situation that you set up is the issue.

Use this UDF. Note the change to thisCell.Formula (rather that FormulaR1C1) to accept a formula (in A1 format) in thisCell as well as a string.
Note also the extra debug.print line which will record the order in which the cells are calculated.

Function DoSame3(theCell As Range) As Variant
    Dim Rform As String
    Application.Volatile
    If theCell.Cells.Count <> 1 Then
        DoSame = "Input must be a single cell."
    Else
        Rform = Application.ConvertFormula(theCell.[COLOR="#FF0000"]Formula[/COLOR], xlA1, xlR1C1, relativeto:=theCell)
        DoSame3 = Evaluate(Application.ConvertFormula(Rform, xlR1C1, xlA1, toabsolute:=True))
        Debug.Print Application.Caller.Address
    End If
End Function

As above, put 100 in A1, the formula =A1+1 in B1
In B2, put =DoSame3($B$1) (B2 will show 1 more than the entry in A2.)
Drag the formula down, expected results.
Drag the formula right, un-expected results.

When I look at the immediate window to see the addresses of the cells in calculation order, it's not logical. I think that order is based more on when the formula was put into a cell rather than the cell's position.