Search for value across multiple columns, workbooks, and sheets then return a cell value?

[qplsn9]

Hello, I want to search for the Label ID in multiple sheets within a workbook. For example, I want to search for "WD734895" in cell A2 in Book1 within the contents of the workbook 2024.01.xlsx and in all sheets within that workbook. Once it is found, I would like to return the cell value that contains the Z#. For example, "WD734895" is searched in 2024.01.xlsx, and if found (located in 2024.01.xlsx cell F14), it will return Z2. I would like to do this will the tray and slot as well.

Along with that, is it possible to do the same above but instead of returning the Z#, if found, it will return the workbook name "2024.01" with the sheet named concatenated on "30" with a "." in between? So search "WD734895" in 2024.01.xlsx and, if found, it will return the value "2024.01.30" in cell C2?

Clarification: for trays and slots, I want to return the tray and slot (located in A2 and B2 respectively for Z1) if "WD734895" is found within the workbook.

 

[felixstraube]

Hi, this could be done with power query and/or VBA, but... and I know what I'm about to tell you is not what you asked, but I'll do it anyways with the intention of saving you some headaches.
In MHO you should structure you database differently. Of course if this isn't an option just ignore the rest of my post.
I tell you what I would do:
I would unify all data in the files and sheets into one sheet and structure the data like so and convert it into a table:

Date	Z#	Label ID	Tray	Slot
28/01/2024	Z1	BE365455	1	1
28/01/2024	Z2	VU243951	2	2
29/01/2024	Z1	GT689981	3	4
29/01/2024	Z1	JS589144	1	1
29/01/2024	Z2	CZ146807	2	1
30/01/2024	Z1	IU121837	1	1
30/01/2024	Z2	JS207598	2	1
30/01/2024	Z2	UV646764	1	1
01/02/2024	Z1	ZH699668	2	1
01/02/2024	Z2	CI588688	3	1
01/02/2024	Z3	HQ314087	1	2
05/03/2024	Z1	JM649688	1	4
05/03/2024	Z1	LF556454	1	1
01/02/2024	Z1	SD731555	1	2
01/02/2024	Z2	BC481578	2	3
01/02/2024	Z3	PT741091	4	4

For every book you create a new row with the date, the Z#, Label ID, Tray and Slot (in any order you like). I know this may be a lot of work but with this you could easily find what you need quick.
You could search by book:

Or by a specific date or dates:

And with your database structured like this you can also do lookups with simple fomulas very easily.

To transform your database you could use power query of VBA to automate the process as much as possible.

I apologice again for not answering you question but i really think you should try this.

 

[qplsn9]

Hi, this could be done with power query and/or VBA, but... and I know what I'm about to tell you is not what you asked, but I'll do it anyways with the intention of saving you some headaches.
In MHO you should structure you database differently. Of course if this isn't an option just ignore the rest of my post.
I tell you what I would do:
I would unify all data in the files and sheets into one sheet and structure the data like so and convert it into a table:

Date	Z#	Label ID	Tray	Slot
28/01/2024	Z1	BE365455	1	1
28/01/2024	Z2	VU243951	2	2
29/01/2024	Z1	GT689981	3	4
29/01/2024	Z1	JS589144	1	1
29/01/2024	Z2	CZ146807	2	1
30/01/2024	Z1	IU121837	1	1
30/01/2024	Z2	JS207598	2	1
30/01/2024	Z2	UV646764	1	1
01/02/2024	Z1	ZH699668	2	1
01/02/2024	Z2	CI588688	3	1
01/02/2024	Z3	HQ314087	1	2
05/03/2024	Z1	JM649688	1	4
05/03/2024	Z1	LF556454	1	1
01/02/2024	Z1	SD731555	1	2
01/02/2024	Z2	BC481578	2	3
01/02/2024	Z3	PT741091	4	4

For every book you create a new row with the date, the Z#, Label ID, Tray and Slot (in any order you like). I know this may be a lot of work but with this you could easily find what you need quick.
You could search by book:

View attachment 102983

Or by a specific date or dates:

View attachment 102985

And with your database structured like this you can also do lookups with simple fomulas very easily.

To transform your database you could use power query of VBA to automate the process as much as possible.

I apologice again for not answering you question but i really think you should try this.

I appreciate the reply! This is a great idea, unfortunately changing the format of our spreadsheets would require a complete overhaul of our entire workflow as the data that is input in the 2024.01.xlsx (aka monthly log) comes from another spreadsheet that is exported and copied and pasted into our monthly log and attempting to change this would require a lot more work. Thank you for your input though!