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

 

[c0barnes]

Interesting thoughts, but not sleek enough. I considered a solution like the one below. I believe only fails because a UDF Function call can't modify the workbook (paste execution is skipped).

Function DoSame(theCell As Range) As Variant
With Worksheets("BLANKHIDDEN").Range(Application.Caller.Address)
theCell.Copy
.PasteSpecial xlPasteFormulas
DoSame = Application.Caller.Parent.Evaluate(.Formula)
End With
End Function


All I really need is some function that transforms an excel formula into "the new formula you would get if you copy/pasted into another cell."

 

[mikerickson]

One possible UDF approach would be

' pseudo-code

Function EvaluateString (formulaString as String, baseRange as Range) As Range
    ' use Application.ConvertFormula to convert A1 style formulaString (relative to baseRange)
    ' to R1C1 formula (relative to Application.Caller) and then evaluate that R1C1 formula
End Function

 

[c0barnes]

Perfect! Thanks!

Function DoSame(theCell As Range) As Variant
    If theCell.Cells.Count <> 1 Then
        DoSame = "Input must be a single cell."
    Else
        DoSame = Application.Caller.Parent.Evaluate(Application.ConvertFormula(theCell.FormulaR1C1, xlR1C1, ToReferenceStyle:=xlA1, RelativeTo:=Application.Caller))
    End If
End Function

I had to put .Caller.Parent in before Evaluate in order to prevent issues when DoSame recalculates while on another tab. Thanks for the tip, Mike. I knew Excel knew how to convert formulas, and assumed there was some VBA access; just try to search for things like "Excel VBA formula convert..." etc and Google has no idea what you want!

 

[c0barnes]

Perfect! Thanks!

Function DoSame(theCell As Range) As Variant
    If theCell.Cells.Count <> 1 Then
        DoSame = "Input must be a single cell."
    Else
        DoSame = Application.Caller.Parent.Evaluate(Application.ConvertFormula(theCell.FormulaR1C1, xlR1C1, ToReferenceStyle:=xlA1, RelativeTo:=Application.Caller))
    End If
End Function

I had to put .Caller.Parent in before Evaluate in order to prevent issues when DoSame recalculates while on another tab. Thanks for the tip, Mike. I knew Excel knew how to convert formulas, and assumed there was some VBA access; just try to search for things like "Excel VBA formula convert..." etc and Google has no idea what you want!

Bad solution - this subverts Excel's dependency tracing causing risk of error.

 

[mikerickson]

@c0barns, I'm not sure what you mean "dependency tracing", but your are right that the DoSame UDF should be made a volatile functin by adding the line

Application.Volatile

 

[c0barnes]

@c0barnes, I'm not sure what you mean "dependency tracing"...

If you have =DoSame($A$1) pasted into a bunch of cells, Excel will only recalculate those cells when A1 or its precedents change. At least, it will only do so on the first calculation pass (1st time you hit F9). But in reality those cells are dependent on whatever A1's shifted formula is. You'll see what I mean if you play with an example.

 

[mikerickson]

That's what the Application.Volitile was for.

It makes the UDF volatile (like Offset or Index) and it will recalculate whenever any cell is changed. Slows the workbook, but it will keep the function current.

 

[c0barnes]

Hmm, it didn't work that way for me even with volatile - I understand that to mean that it will recalculate if precedents change, not just any cell (but I haven't tested that and what I've read was ambiguous). I do have to hit F9 repeatedly, regardless, which intuitively makes sense to me - the cells true precedence isn't defined until DoSame is executed.
Example (with DoSame as defined previously but with Application.Volatile)
A1 = 100;
B1 = A1+10
C1 = DoSame($B$1)
copy C1 to right several cells
update A1 value
B1 and C1 recalculate (their precedents were changed)
D1 and on do not recalculate (though I can't say why they don't, actually, now that I think of it - B1 changed and it's a precedent (but also didn't necessarily have to to justify requiring D1 to recalculate, so it's almost moot))

 

[mikerickson]

Consider these two UDFs

Function OneFtn()
    OneFtn = Sheet1.Range("A1").Value
End Function

Function TwoFtn()
    Application.Volatile
    TwoFtn = Sheet1.Range("A1").Value
End Function

If you put =OneFtn() in a cell and =TwoFtn() in a different cell. and then change A1, you should see what Application.Volatile does.

Adding Application.Volatile to the UDF in post #13 should fix the need to repeatedly F9.

 

[c0barnes]

Consider these two UDFs

Function OneFtn()
    OneFtn = Sheet1.Range("A1").Value
End Function

Function TwoFtn()
    Application.Volatile
    TwoFtn = Sheet1.Range("A1").Value
End Function

If you put =OneFtn() in a cell and =TwoFtn() in a different cell. and then change A1, you should see what Application.Volatile does.

Adding Application.Volatile to the UDF in post #13 should fix the need to repeatedly F9.

Well that makes perfect sense, but it's simply not the result from the sample I wrote out for you. Try it!