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):
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:
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)