I don't know if it's possible using worksheet formulas--well, I know it's not
possible for me!
You could post your question in .worksheet.functions to see if anyone can help.
(Those people live for this kind of stuff.) And after seeing that "B3 = TOYS &
BABY", I'm not sure if it could be done in a formula.
Me on the other hand, I'd cheat and use a UserDefined function.
Do you want to try that?
If yes, paste this code into a general module of your workbook (more about that
later).
Option Explicit
Function mySearch(mySearchCell As Range, ParamArray myRng()) As String
Dim myCell As Range
Dim myRealRng As Range
Dim myElement As Variant
Dim myStr As String
myStr = ""
For Each myElement In myRng
If TypeOf myElement Is Range Then
'do the work
Set myRealRng = Nothing
On Error Resume Next
Set myRealRng = Intersect(myElement, myElement.Parent.UsedRange)
On Error GoTo 0
If myRealRng Is Nothing Then
'do nothing
Else
For Each myCell In myRealRng.Cells
If IsEmpty(myCell) Then
'do nothing
Else
If InStr(1, mySearchCell, myCell.Value, _
vbTextCompare) > 0 Then
myStr = myStr & " & " & myRealRng.Parent.Name
Exit For
End If
End If
Next myCell
End If
End If
Next myElement
If myStr = "" Then
myStr = "Not Found!"
Else
myStr = Mid(myStr, 4)
End If
mySearch = myStr
End Function
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Short course:
Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)
right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side
Paste the code in there.
Now go back to excel.
Then use a formula like:
=mysearch(A1,Toys!A:A,baby!A:A,'Other Sheet!A:A)
