Using Tab Titles to Reference Data?

Copper Contributor

I have manufacturing data which is organized on separate sheets based on manufacturing lots. Each sheet contains information regarding the manufacturer, dates of manufacturing, parts and revisions of parts used, material, QC of the items produced, etc. It's a lot of data on each tab when you take into account QC of several hundred items per tab.

 

I want to have a master sheet that updates information automatically and summarizes some of the more important pieces of this information (such as manufacturer, material) so I can quickly narrow information down in the event that issues should arise. For instance if 1/5 batches has problems, I'd like to be able to sort that information in such a way that I might pick out trends; non-conforming materials,  subs / vendors who may be providing inferior work / products, date of build, etc. 

 

This alone is not hard, I can filter the data when it is in place. The trouble is getting this to fill automatically retroactively and moving forward. I would like to be able fill a column on the master sheet with my internal lot information and have the remaining details fill in. I would just take the extra 20 minutes to fill in the data for each lot produced, but since we now have hundreds if not thousands of lots (sheets) to do this for, it would be simpler to just call the data using my tab labels as a reference set. As an added bonus it would save some time in the future. 

 

All manufacturing lots are based on the same template. So Cell C4 on every sheet is where a part number goes. What I'm trying to do is on the mastersheet put "Lot 234" in cell A1. Cell A2 would call data from C4 on Sheet "Lot 234", using Cell A1 as a reference. I've tried using indirect and vlookup with no luck. I don't really think they are applicable here. That it would autopopulate based on the my entry of the lot name.

 

Any help would be appreciated!

2 Replies

@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.

@Brad Yundt It is in the same workbook. Even in the same workbook I encounter value error. I do believe Indirect is what I want to use, I just can't seem to get it working correctly. In this instance I don't need successive cells. I only need single defined cells that have text or data that I would like to pull into my summary, master sheet. I was hoping do accomplish this by simply entering the tab name into a single cell, and that would trigger to pull data from the tabs into my master sheet.

 

I unfortunately cant share the template for working up a macro.