Indirect formula across sheets

ANTONIO1981

Board Regular
Joined
Apr 21, 2014
Messages
162
Hi all

I Have the following formula in a spreadsheet with several tabs per month with the same layout.

=(March!D$54+March!D$55+March!D$56+March!D$62)/March!D$7

i would like to have the month name written in cell F1.

so when I change F1 to April or other Month name the formula changes picking the information from the April tab.

I tried to write the formula indirect unsuccesfully..

many thanks in advance!

Antonio
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

elmer007

Active Member
Joined
Aug 29, 2014
Messages
299
How about: =(INDIRECT(F1&"!D$54")+INDIRECT(F1&"!D$55")+INDIRECT(F1&"!D$56")+INDIRECT(F1&"!D$62"))/INDIRECT(F1&"!D$7")

Where F1 = "March" and could be changed to April

Let me know if this doesn't work or if you have any questions.
 

shobolos

Active Member
Joined
Sep 7, 2010
Messages
274
=(indirect(f1&"!i$54")+indirect(f1&"!i$55")+indirect(f1&"!i$56")+indirect(f1&"!i$62"))/indirect(f1&"!i$7")
 

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,757
Or :-
Code:
=SUM(INDIRECT($F$1&"!D$54:D$56"),INDIRECT($F$1&"!D$62"))/INDIRECT($F$1&"!D$7")[/code

hth
 

DRSteele

Well-known Member
Joined
Mar 31, 2015
Messages
2,330
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In general, if A1 contains text referring to a sheet (say "Sheet1"), A2 contains text referring to a cell address (say "b1"), and B1 contains a number (say 6), you could have a formula somewhere like this INDIRECT(A1&"!"&A2), which would look for cell Sheet1!B1 and report 6.
 

ANTONIO1981

Board Regular
Joined
Apr 21, 2014
Messages
162
Many thanks elmer007 I will go for your formula as it is very similar to the original formula . thanks to all. Great website and great people. Antonio
 

ANTONIO1981

Board Regular
Joined
Apr 21, 2014
Messages
162
Quick question since the formula contains the cell references betwen " " . The formula seems not being able to change when you move it around. how do you make it relative $. thanks in advance elmer007
 

elmer007

Active Member
Joined
Aug 29, 2014
Messages
299
You're correct that the quotation marks are making the references absolute since excel is storing the text in the formula instead of actual cell references. I'm not sure how your spreadsheet is setup, but I'm going to make a few guesses and give an option...

I'm assuming that you are copying the formula across a row (horizontally) and want the cell references in the formula to change columns. My test sheet has F1 as the cell with the month name in it ("March"), and the following setup would be an option:
In G1 put "D". In G2:G6 put 54, 55, 56, 62, and 7. In H1 put "E". Copy G2:G6 into H2:H6.

In G7 put: =(INDIRECT($F$1&"!"&G1&G2)+INDIRECT($F$1&"!"&G1&G3)+INDIRECT($F$1&"!"&G1&G4)+INDIRECT($F$1&"!"&G1&G5))/INDIRECT($F$1&"!"&G1&G6)


This works by referencing the values in cells G1:G6 instead of having the cell references be stored as text within the indirect formula. So, when you copy G7 and paste into H7, the reference to G1 (which returned "D" in the first column) will shift to H1 (and return "E"- thus making it function like relative cell references).

This setup will probably need to be tweaked to fit your spreadsheet setup, and there may be a better solution, but I find that this works fairly well when I use indirect. If this just doesn't work at all with the way your spreadsheet is setup then maybe we can come up with another creative solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,597
Messages
5,597,085
Members
414,121
Latest member
DamianX88

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