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

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!

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

AmySaha wrote:
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")

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

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



--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"AmySaha" wrote:

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!