0

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

1
  • 1
    I'm not sure this is possible, as what you need is to evaluate 'text' that represents the function.
    – cjb110
    Apr 15, 2014 at 6:52

2 Answers 2

0

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))
-1

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.

1
  • That doesn't do it, that will have the answer from Sheet1 in Sheet2.
    – cjb110
    Apr 15, 2014 at 6:34

You must log in to answer this question.