Get worksheet name based on cell value lookup 1

[Sandman83]

Hi All,

I have a list of phone numbers on the seventh worksheet of a workbook. These same phone numbers appear in multiple worksheets (each number appearing only once across the first six worksheets.)

I'm trying to figure out a formula (tried using VLOOKUP with no success) that will lookup each phone number in the seventh worksheet and return the worksheet name (or even worksheet number would work) that the phone number is found in.

Any help/suggestions would be greatly appreciated.

 

[kjv1611]

As for finding the text, have you looked at using the Find command? You can use Find/Replace via code just like you would manually. So you could set the value of the cell where you want the worksheet name to show equal to the Find function with whatever details are necessary.

I don't think I've tried using it across multiple sheets, so I don't know if you'll have to loop through the sheets, or whether you can just use the Find command/function once..

 

[xlbo]

Easiest way is a bit of hardcoding.

If the phone numbers on the 6 sheets are always in column A then

=if(not(isna(vlookup(A2,Sheet1!$A$2:$A$1000,1,false))),"Sheet1","")&if(not(isna(vlookup(A2,Sheet2!$A$2:$A$1000,1,false))),"Sheet2","")&if(not(isna(vlookup(A2,Sheet3!$A$2:$A$1000,1,false))),"Sheet3","")&...etc etc

pretty nasty though - I'm sure spmeone will tell me there's a clever array formula that will do it - could also do it more smoothly in code using a user defined function but you would have to be happy to use VBA for that

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Sandman83]

Wow! That's about the ugliest thing I've ever seen, lol. Works great though. Thanks a bunch. It never even dawned on me to just hard code the sheet names.

 

[xlbo]

ha ha - sometimes things don't have to be pretty, they just have to work!

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[xlhelp]

How about if you Name all your sheets, such MyShts and then enter the following formula as an array formula? (Use CTRL-SHIFT-ENTER to enter the formula)

=INDEX(MyShts,MATCH(1,--(COUNTIF(INDIRECT("'"&MyShts&"'!A1:A200"),A2)>0),0))

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.

 

[xlhelp]

When I read my post above again, I think I should have elaborated a little more. If you type in names of all your spreadsheets in a column or a row, then name the whole range as MyShts and then use the formula above, it should work well.

Tried and Tested.

Canadian eh! Check out the new social forum Tek-Tips in Canada.
I should live a long time - I eat a lot of preservatives.