Macro to look up and return location

[RattlingCarp3048]

Im looking for some help building a macro to look up the user entered data and return the location data. For example, Ive created a tab called Lookup. I would like the user to enter the data in column A. Then the macro to return the tab name, cell location, title of the column, and folder location of the column.

*Lookup the accession in whole document
*Drawer location will always be row 1 of the same column the accession was found
*Folder location will always be row 2 of the same column the accession was found
*Return "Accession not found" if error

Example
A B C D E

Accession #	Tab Name	Cell Location	Drawer	Folder
NT22011115NT	Drawer10-October	G12	Drawer 10​	2​
WG22014160WG	Drawer9-September	DM21	Drawer 9​	24​
WG22015184WG	macro return	macro return	macro return	macro return

 

[RattlingCarp3048]

bump

 

[shinigamilight]

Can you show us some data? Like where do you need to find Accession? In which column? In which sheet? Is lookup sheet empty?

 

[RattlingCarp3048]

Can you show us some data? Like where do you need to find Accession? In which column? In which sheet? Is lookup sheet empty?

The Lookup tab will only have the example table above. The idea is that the user will input an accession in column A and the macro will populate the locations in columns B-E. The macro will need to search the entire workbook to find the Accession. There are 13 tabs total (12 tabs with data + thw lookup tab).

Formulas instead of macro is also an option if that's an easier route to go.

Currently we are searching one accession at a time via ctrl+find+search entire workbook. However, this is very time consuming as we regularly need to locate 50-200 accession at a time. The ability to use a formula or a macro to search through the entire workbook would definitely speed things up. If it were all on a single sheet a simple lookup formula would suffice but I cannot figure out how one an entire workbook.

 

[RattlingCarp3048]

bump