Search worksheet names?

[Guest]

Is there a way to search through a workbook by first finding a certain
worksheet, then running a vlookup in that worksheet?

EG:
Sheets1~Sheet5
I want to find sheet2, and in sheet2, run vlookup.

 

[Guest]

You can do this programmatically. Code similar to the following will help
you find the sheet you are looking for:

For each sht in thisworkbook.sheets

if sht.name = strCriteria then

'Can return sheet name to a function here.

end if

next

 

[Chip Pearson]

What exactly do you mean by "finding" a certain sheet, and by "running" a
VLOOKUP?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

I'm not very familiar with codes. Can you elaborate abit on what exactly is
needed? Will this code be executed as a macro or can I embed it in the cells?

 

[Guest]

Well here is the equation I use right now to vlookup in multiple sheets in a
workbook:

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

But that runs into problems when the sheets contain the same words being
looked up. So I'm thinking if there is a way to search for a Sheet name, and
then in that Sheet, perform a vlookup.