Forum Discussion
Pulling data to a summary sheet, from select cells in many different sheets
The INDIRECT function can do this IF you change your sheet naming convention to use underscore instead of hyphens. (i.e., MB_1_9_20 instead of MB-1-9-20) For mysterious reasons (to me at any rate) the hyphens caused #REF errors. As soon as I changed the sheet name, no problem.
I added a row across the top to give the cell references to the INDIRECT formula, which ends up, then, pulling the sheet from the name entered in column A, and the cell references from Row 1.
=INDIRECT($A3&"!"&B$1)
This is the formula in cell B3 of your summary sheet. It can be copied, because of the relative and absolute references, to each other cell where you want things summarized.
For example in B2:
=INDIRECT("'"&$A2&"'!B3")
See the attached version.
Warning: the Notes cell is not consistent - it's H13 on one sheet and H15 on another.
- Thank you!
This is very helpful, with your suggestion I do not need to change the name of old batches.
IsaacYes, the suggestion from Hans got around the problem of your use of hyphens in the tab names, by inserting the single quote mark at the start of the string containing the tab's name in the INDIRECT function. It still is worth realizing, that hyphens seem to create problems when used in some names.
I thought it would be easy to show how INDIRECT worked, because I have a similar summary page in a workbook of my own; in fact I make extensive use of INDIRECT for a variety of purposes. In your case, I was surprised that I repeatedly was failing, getting the #REF error, until I changed the name just as an experiment.
