Can I use one formula for several sheets?

0
votes

I have a workbook that currently have 20 sheets and it grows with about 3 sheets per day.
All sheets use the same formulas in columns H-L.
But sometimes I find errors in the formulas or need to ad a condition to it and then I need to update all sheets formulas with the same formula. And that is the problem.

Can I have the formula in one cell somewhere and in each column just reference to this formula so that if I change the formula in this cell it will change in all places?

In each sheet I need the formula to "fill down" about 2300 rows.

In sheet1 

  A1
=A1+B1

In sheet2 

  A1
='sheet1'!A1    // will calculate A1+B1

Anyone know of a way to do this?

2
excelexcel-formula

2 Answers

2
votes

If all sheets have the same structure, you can use a 3D selection to change a formula in the same cell on all sheets.

For example, if your workbook has sheets 1 to 10, select cell B1 in Sheet1, then hold down the Shift key and click Sheet10. Now all cells B1 in all sheets from Sheet1 to Sheet10 are selected, no matter what the sheet name is.

Enter a formula like =A1+A2 and hit Enter. Click any sheet and check the result. B1 will have that formula in each of the sheets.

Now go back to Sheet1, select B1, hold Shift and click Sheet10. Change the formula to =A1*A2 (multiply instead of add) and hit Enter. Copy the formula down.

Check the other sheets and see that the same formula has been applied and copied down on all other sheets.

With this technique, you can select the same cell in all sheets of the workbook and change the formula in all sheets with just one edit. Of course, it only works if all sheets in the selection have the same data structure.

Edit after comment: You could use the first sheet as the Formula Master sheet, and just need to remember that if you want to change a formula, you first have to use a 3D selection. Other than that, Excel does not offer formula replication in the way you describe. A reference to a cell will always return the cell's value, not its formula.

0
votes

You could create a UDF (user defined function) in VBA for your goal:

Press Alt+F11, right click on your project in the project explorer and select Insert --> Module. Paste the code below:

Function ExecuteFormula(ByVal Target As Range)

   On Error Resume Next

   ExecuteFormula = Evaluate(Target.Formula)

End Function

Now you can use the ExecuteFormula() like every other function. If you want to enter the formula in your master sheet as a string "=A1+B1" just replace the 3rd line with:

   ExecuteFormula = Evaluate(Target.Value)

I hope this helps.