Indirect formula across sheets

[ANTONIO1981]

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

 

[elmer007]

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]

=(indirect(f1&"!i$54")+indirect(f1&"!i$55")+indirect(f1&"!i$56")+indirect(f1&"!i$62"))/indirect(f1&"!i$7")

 

[ukmikeb]

Or :-

=SUM(INDIRECT($F$1&"!D$54:D$56"),INDIRECT($F$1&"!D$62"))/INDIRECT($F$1&"!D$7")[/code

hth

 

[DRSteele]

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]

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]

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]

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.