How to fill cell references from several worksheets in one?

[Jenykell]

Ok, so I have a workbook, each sheet is an order form, I want to reference
the same cell on each sheet on a Master Tab that takes the info from each
individual sheet and copies it to the master tab. I have referenced the cell
on the first sheet (1) with an absolute cell reference (ie: ='1'!$C$1) and I
have about 50 columns of this data. I would like to fill down and have the
'1' from the formula change to 2, 3, 4, 5, etc. You would think there would
be an easy way to do this. Anyone know how? I have been working on it for a
while with no luck. At this point, it would probably have been faster to
just manually change it.

Ideas?

Jen

 

[Gord Dibben]

In A1 of Master sheet.

=INDIRECT("'" & ROW() & "'!$C$1")

Copy down.


Gord Dibben MS Excel MVP

 

[ND Pard]

Because the sheets you want to reference are in the same workbook, you might
consider using the Indirect Function.

If you have 50 sheets, put the numbers 1 through 50 in consective rows.
Assume you put these in rows A1 through A50.

Then your formula:

='1'!$C$1

could be changed to:

=Indirect("'" & A1 & "'$C$1")

or

=INDIRECT("'"&A1&"'!"&CELL("address",C1))


This way you can copy the formula up/down or right/left

Good Luck.
This should return the same results as your original formula and can now be
copied down 50 rows.