I recently needed to extract data from a series of tables on separate worksheets, into a form suitable for plotting on an XY graph. In Lotus 123 this would be a piece of cake, since the Lotus @INDEX function has an optional third parameter allowing the sheet number to be selected. Unsurprisingly, I could find nothing on the subject in the Excel help files, but surprisingly I couldn’t find anything on the Internet either. I ended up using the INDIRECT() function, as shown in the screen shot below (click image to see full size):
Retrieve data from a 3D range using INDIRECT()
I have entered sheet names in column B and the cell address in row 3, then the formula to retrieve the data to go in cell D5 is:
=INDIRECT(“‘”&$B5&”‘!”&D$3)
Note the $ signs that keep the address referring to the correct column and row when it is copied, and the apostrophe between the double quotes at the start and before the exclamation mark. Also note that the sheet names in column B are now just the name as it appears on the tab, without the !.
Does anyone have an alternative method?
Is there a neat way to fill the sheet name column automatically?
Edited 27 August 09 following comments from Harlan Grove, who also provided some alternative ways of tackling it (see comments below)
Newton Excel Bach, not (just) an Excel Blog 
Yes, use the INDIRECT function with the ADDRESS function which will let you specify sheet name.
LikeLike
NEVER waste storage or cycles using INDIRECT(ADDRESS(…))!
I guess you didn’t check the Excel newsgroups because this has been discussed many times in them.
=INDIRECT(“‘”&worksheet_name&”‘!”&range_address)
is the canonical form.
The single quotes on either side of worksheet_name are unnecessary but cause no problems when worksheet_name contains only letters, numerals, periods and underscores, but it avoids #REF! errors when there are other characters like spaces.
That said, INDIRECT is volatile, so if there were many formulas calling INDIRECT or other volatile functions, you can slow down Excel recalculation noticeably. If you have fewer than 30 worksheets to process, one alternative is
=INDEX(CHOOSE(MATCH(worksheet_name_sought,
list_of_worksheet_names,0),worksheet_01!$D$3:$G$9,
worksheet_02!$D$3:$G$9,…),row_num,col_num)
This works in part because CHOOSE returns references to ranges rather than arrays of range values, so this same approach works with COUNTIF and SUMIF. The caveat is that CHOOSE’s 2nd and subsequent arguments must be in the same order as list_of_worksheet_names.
Then there are UDF approaches, one of which being to pass the top-left cell in the first worksheet as the first argument, the bottom-right cell in the last worksheet as the second argument, and row, column and sheet numbers as the remaining arguments. Crude, but it works as long as you don’t move worksheets perversely.
Finally, there’s Laurent Longre’s MOREFUNC.XLL add-in which provides a function named THREED which takes a 3D reference as its argument and returns a 2D array, e.g., for 2 sheets (S), 3 rows (R) and 4 columns (C), the resulting 2D array would be arranged as
S1R1C1 S1R1C2 S1R1C3 S1R1C4
S1R2C1 S1R2C2 S1R2C3 S1R2C4
S1R3C1 S1R3C2 S1R3C3 S1R3C4
S2R1C1 S2R1C2 S2R1C3 S2R1C4
S2R2C1 S2R2C2 S2R2C3 S2R2C4
S2R3C1 S2R3C2 S2R3C3 S2R3C4
so worksheet range values are stacked vertically, which makes sense since Excel provides more rows than columns.
LikeLike
Harlan – I guess I didn’t look hard enough, but thanks for the detailed response. I have modified the post as suggested to allow for spaces in the sheet names (which has the added benefit that sequentially numbered sheets can now be listed with a drag and fill).
I should have thought of Morefunc (although you learn more if you write your own of course)
LikeLike