Sheet Referencing - autofilling sheet names

[Pat]

Is there a way to easily reference sheet names.

Here is my problem:

i have several sheets named 01,02,03,04... and so on,
I have a summary sheet that i would like to reference all the individual
sheets.
I start at the top and refernce sheet 01, then i would like to drag down so
that the next row is referencing sheet 02 and so on. This does not happen
when i drag the cell down.

Is there any way to achieve this. I tried typing out the cell names in
another column and referencing that cell inside the ' ', but that didn't seem
to work.

Thanks,

Pat

 

[RagDyeR]

Say your sheet name list starts in A2:
01
02
03
etc...

And you want to return the contents of D1 from each sheet.

Enter this in B2, and copy down:

=INDIRECT("'"&A2&"'!D1")

*NOTE* - Your list in Column A should be TEXT,
*NOT* numbers formatted to display the leading zero!
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Is there a way to easily reference sheet names.

Here is my problem:

i have several sheets named 01,02,03,04... and so on,
I have a summary sheet that i would like to reference all the individual
sheets.
I start at the top and refernce sheet 01, then i would like to drag down so
that the next row is referencing sheet 02 and so on. This does not happen
when i drag the cell down.

Is there any way to achieve this. I tried typing out the cell names in
another column and referencing that cell inside the ' ', but that didn't
seem
to work.

Thanks,

Pat

 

[Ashish Mathur]

Hi,

Try this.

Press Ctrl+F3 and click on New. In the name box, type wkst_names and in the
refers to box, type =GET.WORKBOOK(1)&T(NOW()). Now in cell C4, type

=MID(INDEX(wkst_names,ROW()-ROW($C$3)+1),SEARCH("]",INDEX(wkst_names,ROW()-ROW($C$3)+1),1)+1,10).
you may now copy this formula down

In the formula, please change the reference of the row function to one row
less than the cell in which you are pasting the formula.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com