How do I create a query field in Excel

S

Sean

I have a sheet with 9000 part numbers on it. Is there a way of say at the top
of the sheet having some sort of search box, so I can type in the part# and
it goes straight to that part?

Thanks
 
S

sebastienm

Hi,

Method1: Using the Filter feature.
-select a cell in the data
-goto menu Data>Filter>AutoFilter
- now your data header has dopdown boxes. From the part# header select the
part#. This will make all show only rows with this part#

Method2:
-Select column Part#
- menu Edit >Find

Method 3: with code
- In a code module, paste the following sub FindAndGo
Also change the 'CHANGE HERE' section to fit your particular case
'------------------------------------------------------------------
Sub FindAndGo()

Dim rgEntry, rgPart As Range, rg As Range

'--- CHANGE HERE -----
Set rgEntry = Range("C1") 'value to search for
Set rgPart = Range("A:A") 'range of Part# to search
'------------------------

'Find it and go there
Set rg = Range("A:A").Find(What:=rgEntry.Value, LookIn:=xlValues, _
LookAt:=xlWhole)
If Not rg Is Nothing Then
Application.Goto Reference:=Application.ConvertFormula( _
rg.Address, xlA1, xlR1C1, True), scroll:=True
End If

End Sub
'-------------------------------------------------------------

- in the above code, the searched value is in C1
Next to this cell, add a button from the Forms toolbar
Right Click the button and choose Assign Macro from the pop-up menu.
Choose the FindAndGo macro from the list
Now enter a value in C1 and click the button. If the value exists, you'll be
sent to the corresponding cell.
 
Top