Another search issue

[stephen.carter2]

Hi there,
I have 1 workbook, with 15 sheets in it.
The first has a list of names, eg bloggsj, smithb etc etc in column A.
The other sheets have multiple columns with varied information.

I'd like to do a 'find and ''log''' search, whereby I have the name in
column A, and the search is in column B.

If the name in A is found in the other sheets, then I just want it to
say "Yes, Found" or "No, not here"
What would be nice is some way in column C to identify the sheet and
cell the entry is in, as shown in the find all search.

I would use this but I have over 100 entries to find.

I've tried several things using the IF statement but can not seem to
get the statement to cover the whole of one sheet.

Anybody have any ideas as they will be gratefully recieved.
Thanks
Stephen

 

[Don Guillett]

One way that simply puts the found address in col b
Sub findem()
Columns(2).ClearContents
For Each c In Range("a2:a44")
On Error Resume Next
For Each ws In Worksheets
If ws.Name <> "Sheet1" Then
If Not ws.Cells.Find(c, lookat:=xlWhole) Is Nothing Then
c.Offset(, 1) = ws.Name & ws.Cells.Find(c, lookat:=xlWhole).Address
End If
End If
Next ws
Next c
End Sub

 

[Don Guillett]

You said no code so
=SUMPRODUCT(COUNTIF(INDIRECT("'"&{"b","c","d","e"}&"'!a1:h10"),A2))