Reference a single formula across multiple sheets

Rob1st

New Member
Joined
Oct 23, 2015
Messages
9
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,170
Office Version
  1. 2010
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

New Member
Joined
Oct 23, 2015
Messages
9
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
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")

Code:
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
 
Last edited:

Rob1st

New Member
Joined
Oct 23, 2015
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 23, 2015
Messages
9

ADVERTISEMENT

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,256
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Oct 23, 2015
Messages
9
I am assuming this is not possible then? Shame as I think it would be awesome to have.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,636
It can be done, with some difficulty. For example:

ABC
1117711

<tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
A1=B1/7
C1=Formula1

<tbody>
</tbody>

<tbody>
</tbody>

Workbook Defined Names
NameRefers 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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,550
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top