Using Tab Titles to Reference Data?

ABosley315 

When you say "organized on separate sheets based on manufacturing lots," an Excel expert says "Great. We can use INDIRECT to retrieve data from that worksheet." The formula below does that, and may be copied down and across if you need to get data from successive cells.

=INDIRECT("'" & $A$1 & "'!" & CELL("address", C4))

When you say INDIRECT doesn't work for you, I wonder if you meant separate workbooks rather than separate worksheets. The distinction is important because INDIRECT returns #VALUE! error when the target workbook is closed.

If I am correct, one workaround would be to use a macro to open the target workbook and import its data. Although VBA code can retrieve data from a closed workbook, it's a fairly slow process. Once you exceed say 20 items, it is faster to open the target workbook, capture its data, then close it. Since you need several hundred items per tab, that is definitely the way to go.

Next, I am going to assume that writing VBA code is not your strong suit. I'd be glad to write the code for you--but you need to post a workbook with your master sheet as well as one for a typical manufacturing lot. The code would live in your master workbook, and could be triggered either by a button or a user selection of a particular manufacturing lot.

The macro needs to know how the target workbook is named, and where it might be found. Although the macro could display a file browse dialog, it is nicer for the user if the process occurs automatically.