Reference a single formula across multiple sheets

[Rob1st]

Tried searching for this, but can't quite find what I need.

I have a workbook with lots of pages, every page is structured exactly the same, but the information within it varies.

I have a template sheet set up, so that if I want to change the labels on the sheet, I just change them on the template sheet, and they all change, easy.

The formulas within each sheet are also identical, so can I do this:

1, Place the formula on the template
2, Call the formula on each sheet from the template

Example:

Every sheet has a =SUM(A10:A220) formula in cell A1

Can I have this formula in Template!A1 as text and then somehow call that cell in A1 of every sheet, to work in every sheet?

I tried =INDIRECT(Template!A1) No Luck, but I am assuming there must be a way.

Why do I want this?

Well if I have a reference sheet where I can amend formulas once and it applies, instead of correcting formulas on every sheet, then it will save me lots & lots of time.

If the answer is in VBA I am also happy to consider, just have not found anything through searching.

Appreciate the help.

 

[arthurbr]

When you group all sheets ((while holding down the Shift button and clicking the first and the last tab- selected tabs are then highlighted)),
any formula you enter will be automatically entered in all selected sheets at the same place.
Perhaps you can record a small macro to group the sheets to do so

 

[Rob1st]

I didn't know that, very helpful. Thank you

However, I'm still keen to find if there is a way to reference a single formula string across multiple sheets.

 

[My Aswer Is This]

Try this Vba Solution:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the "Template" sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

When you change the formula in Range "A1" of the sheet named "Template" the same formula will be put into all sheets in Range("A1")

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> "Template" Then Sheets(i).Range("A1").Formula = Sheets("Template").Range("A1").Formula
    Next
End If
End Sub

OK

 

[Rob1st]

Excellent, Thank you.

Now if I want to do this for a sheet full of formulas I assume I expand A1 to be the Range needed. Do I make each range a separate Sub?

And lastly, I have a plan to add a summary sheet, how would I get it to exclude the summary sheet if it were in the same workbook (I know a quick fix is to make a separate workbook and externally link).

Thanks for the help!

 

[My Aswer Is This]

Your going to go into a sheet Named "Template and change a large number of formulas and then want all these formulas to change on all your sheets.

That sounds like a lot of work even if just done on one sheet.

Show me a example of a formula the way it looks now and the way it will look after you change it.

Is there is any trend to how the formulas change.
Ignoring the sheet named "Summary would be no problem.

 

[Rob1st]

Yep, this is why I thought I might be able to do it by using indirect.

An Example:

Every sheet has an formula =INDEX(Specs!D4:D50,MATCH($E$2,Specs!C4:C50,0))

The Specs sheet where it references never changes, and the Cell E2 is the reference for every sheet, hence why I thought I may be able to use INDIRECT.

I use INDIRECT in another sheet like this: =IF(C1="","",INDIRECT(CONCATENATE("'",$D8,"'!",$E$1))) and it works perfectly, So why does INDIRECT not work if I call a cell that has a formula written in Text?

 

[My Aswer Is This]

I know more about scripting then Formulas. Maybe someone else here at Mr. Excel will be able to help you.
I will continue to monitor this thread and see if I can learn something here.

 

[Rob1st]

I am assuming this is not possible then? Shame as I think it would be awesome to have.

 

[Eric W]

It can be done, with some difficulty. For example:

	A	B	C
1	11	77	11

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula A1 =B1/7 C1 =Formula1 <tbody> </tbody>

<tbody>
</tbody>

Workbook Defined Names Name Refers To Formula1 =EVALUATE(MID(FORMULATEXT(Sheet1!A1),2,999)) <tbody> </tbody>

<tbody>
</tbody>

Cell A1 has a formula, B1 a value. Now go to the Formulas tab, click Define Name. In the Name box put Formula1 (or whatever you like), and in the refers to box put:

=EVALUATE(MID(FORMULATEXT(Sheet1!A1),2,999))

and click OK. In C1, put

=Formula1 and you should get the same answer as A1. Change the formula in A1, and C1 should match. Now go to Sheet2, put a number in B1, and =FORMULA1 in C1, and you should get the correct value using the Sheet1!A1 formula and the Sheet2!B2 value. But this is a bit tricky. The A1 value in the formula likes to change to other ranges for no reason I understand. But if you get it right, it will work.

Possibly easier to do is create a UDF that handles the Evaluate a bit easier. See this link:
http://www.mrexcel.com/forum/excel-questions/62067-eval-function-without-morefunc-add.html

That could be adapted to your use if you want.