Pulling data to a summary sheet, from select cells in many different sheets

[Isaaccc]

Hi,

I have an excel workbook where I keep track of production, each day we make one of several products, and the calculations, instructions, and notes of a given batch are all stored in a single sheet. All sheets are structured the same (e.g. batch room temperature is in always in cell B6, and Viscosity is always in cell B23). 

Every day I start by duplicating a template sheet of a given product, change the name of the duplication to todays batch number, and fill in the sheet.

What I would like is a simple way to have a summary table (in a separate sheet), where after each batch is added, it would be easy to update the table. I would like to enter the batch name, which is similar to the name of the sheet, and have excel automatically pull data from the relevant cells (eg, from B3 for Colom B, from H12 for Colom I). 

Attached is a demo workbook to show what I am trying to do.  It has templates for there different products, two sheets with data on two batches, and a summary sheet where I need help.

Would appreciate any help in how to make this work. 

thanks,

Isaac

[#2 · Accepted solution]

@Isaaccc 

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.

[#3]

@Isaaccc 

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.

[#4]

@mathetes 

Thank you!

I have been racking my brain on this for a week

[#5]

Thank you!
This is very helpful, with your suggestion I do not need to change the name of old batches.

Isaac

[#6]

@Isaaccc 

Yes, 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.