Formula Based on Multiple Sheets

[Neil M]

I have a workbook with a summary sheet for 25 other sheets. On these 25
sheets there is a cell that is the same cell on every sheet, but may contain
different values based on formulas within the specific sheet.
In other words, cell D17 on sheet '1' will be different from D17 on any
other sheet.

On the summary sheet row 1 would refer to sheet 1. I need the cells in row 1
of the summary sheet to look for specific values in a specific cell on sheet
1 and simply copy the value.
When I simply enter ='1'D17 it works, But when I try to copy that formula
down the column it comes out in row 2 as '1'D18 instead of '2'D17.

I either need a formula that will reference the proper sheet or find a way
to enter the formulas in the first row and be able to copy them all the way
down and only change the sheet number, NOT the cell number.

I hope I have provided enough information.

 

[T. Valko]

Try this...

Assume you enter the first formula in cell A1:

=INDIRECT("'"&ROWS(A$1:A1)&"'!D17")

Copy down as needed.

 

[Neil M]

That didn't really do anything. Maybe if I gave exact sheet numbers/names and
the cell numbers.

On sheet 'Master Totals' row 4 represents totals from sheet '1'

B4 should equal cell i2 on sheet '1'
C4 should equal cell Q14 on sheet '1'
D4 should equal cell Q16 on sheet '1'
E4should equal cell Q7 on sheet '1'
F4 should equal cell Q21 on sheet '1'
G4 should equal cell Q22 on sheet '1'
H4 should equal cell Q23 on sheet '1'

Once I get that row completed I want to drag and copy down the formulas so
that row 5 equals the exact same cells on sheet '2'. row 6 on sheet '3' and
so on.

I don't mind having to do one column at a time. Right now I am entering them
one formula per cell at a time.

 

[T. Valko]

B4 should equal cell i2 on sheet '1'

C4 should equal cell Q14 on sheet '1'
D4 should equal cell Q16 on sheet '1'
E4should equal cell Q7 on sheet '1'
F4 should equal cell Q21 on sheet '1'
G4 should equal cell Q22 on sheet '1'
H4 should equal cell Q23 on sheet '1'

Ok, since there's no pattern to the cells being referenced then you'll have
to enter an individual formula for each of those:

B4: =INDIRECT("'"&ROWS(B$4:B4)&"'!I2")
C4: =INDIRECT("'"&ROWS(C$4:C4)&"'!Q14")
D4: =INDIRECT("'"&ROWS(D$44)&"'!Q16")
E4: =INDIRECT("'"&ROWS(E$4:E4)&"'!Q7")
F4: =INDIRECT("'"&ROWS(F$4:F4)&"'!Q21")
G4: =INDIRECT("'"&ROWS(G$4:G4)&"'!Q22")
H4: =INDIRECT("'"&ROWS(H$4:H4)&"'!Q23")

Then, select B4:H4 and copy down as needed.

 

[Gord Dibben]

In B4 enter =INDIRECT("'"&ROWS(A$1:A1)&"'!I2")

In C4 enter =INDIRECT("'"&ROWS(A$1:A1)&"'!Q14")

Changing to Q16, Q7 as you enter your formulas one at a time across to H4

When that is done, select B4:H4 and simply drag/copy down as far as you
need.

The "ROWS" function will change the sheet names to 2, 3, 4 etc. and the cell
references will rermain fixed.


Gord Dibben MS Excel MVP

 

[Neil M]

In the words of Jeff Spikoli.....

AWESOME! TOTALLY AWESOME!!!!

Thank you all so very much.