I would like to be able to pull a formula through onto a second tab (not copy a formula). For example on sheet 1 I have a range of cells that calculate amounts (cell A) divided by a number of months (3 or 5 etc) (Sheet 1 Cell B =+A/3). The second spreadsheet needs to use the same formula (sheet 2 Cell B = +A/3). I want to be able change the forumla on sheet 1 and have the forumla automatically update on sheet 2. I also need to write in allowances that the cell could be null. Thanks
2 Answers
You would need VBA to accomplish this. If it should be real-time, and not require a macro be run manually, then the following would do it:
Create a User-Defined Function to extract the formula as a string:
Function GetFormula(Target As Range) As String
GetFormula = Target.Formula
End Function
Create another to evaluate a string like a formula:
Function Eval(Ref As String)
Application.Volatile
Eval = Evaluate(Ref)
End Function
Put this in your "Sheet 2 Cell B":
=eval(getformula(Sheet1!B1))
When you are in a cell in the second tab, type "=", then click on the first tab, choose the cell that contains the original formula and press enter.