Any Way to Save Eons of Typing? Automating 3D Reference to Sheet N

[AmySaha]

I have a workbook with 600 worksheets. Every worksheet is named by the same
naming convention "Table1", "Table2", etc. and data is organized in the same
fashion on every sheet.

I need to return on a Summary sheet the values from cell B13 next to a
column containing the hyperlink reference. I've been using a 3d Reference,
"Table1!B13", "Table2!b13", etc. I have no problems changing or manipulating
the cell value, B13, as needed. The problem is that I am endlessly typing
"Table1, Table2...Table600". Or, I am hoping to avoid doing this. Seeing
that the value I want next to the word Table is already there for me in
Column A,(i.e. hyperlink 1, 2...600 ) I don't see why I have to retype it.
Why can't I return the value of the cell, something like " 'Table(A1)'!b13 ",
and just drag it down 600 rows. But I can't seem to get the 3D reference to
recognize a formula within the sheet name, only in the cell reference
manipulations. I don't need to sum these values, I need to create an
unalterated Table of their values, so as far as I can tell, the Index and
Consolidate function wouldn't help me (though I'm none too familiar with
either)

Much appreciated!

 

[Glenn]

I have a workbook with 600 worksheets. Every worksheet is named by the same
naming convention "Table1", "Table2", etc. and data is organized in the same
fashion on every sheet.

I need to return on a Summary sheet the values from cell B13 next to a
column containing the hyperlink reference. I've been using a 3d Reference,
"Table1!B13", "Table2!b13", etc. I have no problems changing or manipulating
the cell value, B13, as needed. The problem is that I am endlessly typing
"Table1, Table2...Table600". Or, I am hoping to avoid doing this. Seeing
that the value I want next to the word Table is already there for me in
Column A,(i.e. hyperlink 1, 2...600 ) I don't see why I have to retype it.
Why can't I return the value of the cell, something like " 'Table(A1)'!b13 ",
and just drag it down 600 rows. But I can't seem to get the 3D reference to
recognize a formula within the sheet name, only in the cell reference
manipulations. I don't need to sum these values, I need to create an
unalterated Table of their values, so as far as I can tell, the Index and
Consolidate function wouldn't help me (though I'm none too familiar with
either)

Much appreciated!

You need the INDIRECT function. Something like this:

=INDIRECT("'Table"&A1&"'!B13")

 

[ryguy7272]

600 sheets? WOW!! Try this:
http://www.rondebruin.nl/copy2.htm