Find and return name of worksheet tab for referenced cell

[MMangen]

I have a spreadsheet with many differently named tabs.

On the first tab there are a list of names for client who work at different
offices.

I'd like to have a formula find that client's name on the following
worksheet tabs and then in a cell next to that name to tell me which tab the
record appears.

So for example:
On first tab: Michelle

The information for Michelle is on the Ohio tab

Can the formula go out and find Michelle's name on the Ohio tab and then put
the "Ohio tab name" in the cell next to her name?

Thanks!

 

[Bernie Deitrick]

MMangen,

You can do it easily with a USer-Defined-Function. The code is below, copy it into a standard code
module in your workbook, and use it like

=Where(A1)

with Michelle in cell A1, it will return the first sheet name where Michelle is found, or "Not
Found" if Michelle appears on no other sheet than the first sheet.


HTH,
Bernie
MS Excel MVP


Function Where(myV As Variant) As String
Dim myS As Worksheet
Dim myP As String
Dim myR As Range

Where = "Not Found"

myP = Application.Caller.Parent.Name
For Each myS In Worksheets
If myS.Name <> myP Then
Set myR = myS.Cells.Find(myV)
If Not myR Is Nothing Then
Where = myS.Name
Exit Function
End If
End If
Next myS
End Function

 

[MMangen]

Bernie:

Thanks, will try this out and will let you know how it worked!