Search and Display Sheet Name

[Guest]

What formula would I use for this process:

I'm putting this formula in cell B2 of worksheet 1.
First: take the number that has been entered in cell A2 of worksheet 1
Second: Search through worksheets 3-24 and find that number (the number will
be unique through out the worksheets being searched)
Third: Display the name of the worksheet it was found in.

I'm hoping this is possible without using VBA. If it is going to need VBA
please bare with me since I have never used it.

Thank You For The Help!

 

[Jason Morin]

Searching an entire sheet is requires some serious
processing power and probably isn't necessary. Let's
assume that you want to examine A1:A100 on each sheet.

1. List the sheet names to search down col. A, starting
in A5.

2. Put this formula in B5 and copy down:

=SUMPRODUCT(--EXACT(INDIRECT("'"&A5&"'!A1:A100"),$A$2))

3. Now use this to pull in the Sheet name with the value:

=INDEX(A5:A26,MATCH(1,B5:B26,0))

HTH
Jason
Atlanta, GA

 

[Guest]

hi,
I could be wrong but searching 24 sheets might be a little
much for a formula.
could you get by with a button?
put a button a cell B2. right click the button then click
view code. paste the following into the vb editor
Private Sub CommandButton1_Click()
Dim sStr As String
Dim sh As Worksheet
Dim rng As Range

Sheets("sheet1").Select
sSrt = InputBox("Enter item to search for")
'sStr = sheets("sheet1").Range("A1")
For Each sh In ThisWorkbook.Worksheets
If sStr <> "" Then
Set rng = Nothing
'sh.Activate
Set rng = sh.Range("A1:IV65536").Find(What:=sStr, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If
If Not rng Is Nothing Then
MsgBox "Found on sheet " & sh.Name & " at cell " & _
rng.Address
'rng.Select
Exit Sub
End If
Next sh
If rng Is Nothing Then
MsgBox sStr & " was Not found"
End If

End Sub
This will display an inputbox, search all sheets for what
was input, tell you where is was found.
It will not find multiple instances.
if you want it to take you to the item then, uncomment the
lines sh.activate and Rng.select.
if you want it modified, repost you preferences.
Regards
HDR
Atlanta Ga

 

[Guest]

The second formula with index, would that be placed in B2?

 

[Guest]

How do you put in a button?

hi,
I could be wrong but searching 24 sheets might be a little
much for a formula.
could you get by with a button?
put a button a cell B2. right click the button then click
view code. paste the following into the vb editor
Private Sub CommandButton1_Click()
Dim sStr As String
Dim sh As Worksheet
Dim rng As Range

Sheets("sheet1").Select
sSrt = InputBox("Enter item to search for")
'sStr = sheets("sheet1").Range("A1")
For Each sh In ThisWorkbook.Worksheets
If sStr <> "" Then
Set rng = Nothing
'sh.Activate
Set rng = sh.Range("A1:IV65536").Find(What:=sStr, _
After:=sh.Range("A1"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End If
If Not rng Is Nothing Then
MsgBox "Found on sheet " & sh.Name & " at cell " & _
rng.Address
'rng.Select
Exit Sub
End If
Next sh
If rng Is Nothing Then
MsgBox sStr & " was Not found"
End If

End Sub
This will display an inputbox, search all sheets for what
was input, tell you where is was found.
It will not find multiple instances.
if you want it to take you to the item then, uncomment the
lines sh.activate and Rng.select.
if you want it modified, repost you preferences.
Regards
HDR
Atlanta Ga

 

[Jason Morin]

Yes, you could put in B2. Or any other open cell.

HTH
Jason
Atlanta, GA