VLOOKUP type of Macro to search and find through an entire spreadsheet (workbook)

You haven't really made clear how you want to use this information... But have a look at this, which will generate a couple of arrays, contianing the sheets where the Employee reference is found, and the rates in each sheet.

Please Login or Register  to view this content.

For the example workbook you attached, what does your OUTPUT look like? What do you want to end up with?

Okay - I've changed the code to a function, so you can use it on the worksheet:

Please Login or Register  to view this content.

The function requires three arguments.
1. Employee - the employee string to be found in all worksheets
2. OutputType - 1 for Sheet Name, 2 for Pay Rate
3. ExcludeCurrent (optional) - false or omitted to include all worksheets, true to exclude the current worksheet.


Use as:

	F	G
1	Tabs	Pay Rate
2	=employeedetails($A2,1,TRUE)	=employeedetails($A2,2,TRUE)
3	=employeedetails($A3,1,TRUE)	=employeedetails($A3,2,TRUE)
4	=employeedetails($A4,1,TRUE)	=employeedetails($A4,2,TRUE)
5	=employeedetails($A5,1,TRUE)	=employeedetails($A5,2,TRUE)

Which returns:

	F	G
1	Tabs	Pay Rate
2	Sheet2,Sheet4	20,5
3
4	Sheet3	10
5	Sheet2,Sheet3,Sheet4	30,15,15

Happy to help

You can click "Add Reputation" on posts you found useful