Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Search and Display Sheet Name
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! |
#2
|
|||
|
|||
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! . |
#3
|
|||
|
|||
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! . |
#4
|
|||
|
|||
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! . |
#5
|
|||
|
|||
How do you put in a button?
" 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! . |
#6
|
|||
|
|||
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! . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup help | Excel Discussion (Misc queries) | |||
Search within formula | Excel Worksheet Functions | |||
Display specific rows from table on other worksheet | Excel Discussion (Misc queries) |