Formula Based on Multiple Sheets


N

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.
 
Ad

Advertisements

T

T. Valko

Try this...

Assume you enter the first formula in cell A1:

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

Copy down as needed.
 
N

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

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$4:D4)&"'!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.
 
G

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
 
Ad

Advertisements

N

Neil M

In the words of Jeff Spikoli.....

AWESOME! TOTALLY AWESOME!!!!

Thank you all so very much.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top