Performing a Seacrch on Multiple Sheets and Returning the Column Header and Sheet Name

Do you have to have it set up in this way? That is, with separate sheets for buildings and separate columns for workstations? It might be a lot easier to manage if you had, say two sheets. The first would be the details of each unique part ... part number, description, cost, whatever. And the other would have building, workstation, part number. You could also have a third sheet with details of the workstation; maybe building code, floor, reference, user(s), whatever. And even a sheet for the buildings.

I know that means that you'd have up to four sheets but this structure would enable you to filter and analyse the data much more easily.

Regards, TMS

Well, if it were me, I would have one sheet. Column A would have the building code/name, Column B would have the workstation ID and column C would have the Part Number. You could use Data Validation for consistent input of the building code, and possibly the workstation ID, and even the part number but that would require a list of unique part numbers. You could then use Autofilter to filter on the building and/or workstation and/or part number. You could use a Pivot Table to analyse the list, again, filtering on building and/or workstation. If you did go for a list of unique part numbers, you could use COUNTIFS or SUMPRODUCT to work out how many parts are where.

Although you would need to repeat the building and workstation ID code, you could be clever with Conditional Formatting to "hide" the repetition so you could, if required, print a full list without seeing all the repeats. You want to see where a part is, you filter on the part number ... simple. You only want to look at one building ... you filter on the building code. And if you want to see the 1600 parts for that workstation, you just filter on the workstation ... easy.

I'm assuming that if you have part numbers, they are either self descriptive or there is a parts list somewhere that can be used as reference data.

Just my thoughts. You have all the data already so it would be fairly straightforward to consolidate it with copy and paste and Autofill to copy down the codes. Maybe an hour to do and a lifetime of pain saved (IMO).


Regards, TMS

You're welcome. Thanks for the rep. Good luck with the consolidation ... tedious but worthwhile!