Search and Display Sheet Name

[jtinne]

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


>-----Original Message-----
>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!
>
>.
>

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
>-----Original Message-----
>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!
>
>.
>

[jtinne]

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

"Jason Morin" wrote:

> 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
>
>
> >-----Original Message-----
> >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!
> >
> >.
> >
>

[jtinne]

How do you put in a button?

"[email protected]" wrote:

> 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
> >-----Original Message-----
> >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]

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

HTH
Jason
Atlanta, GA

>-----Original Message-----
>The second formula with index, would that be placed in
B2?
>
>"Jason Morin" wrote:
>
>> 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
>>
>>
>> >-----Original Message-----
>> >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!
>> >
>> >.
>> >
>>
>.
>