Excel search

[h.j.barton]

I want to create a search, whereby entering a number in one cell (SHEET 2-B1), searches for that same number in another set of cells (SHEET 1-A2:A11), and then copies the contents of another (from sheet 1).

Example:
I want to be able to enter any item number into B1 (sheet 2), and find the following information about that specific product (from sheet 1):
sheet 2--B2: name of product
sheet 2--B3: type of product
sheet 2--B4: location of product
sheet 2--B5: price of produce
sheet 2--B6: whether or not this item is on sale.

SHEET 1: data
__A B C D E F
1 ITEM #/ name/ type/ location/ price/ sale
2 111 /apple /fruit /A1 /$1.00 /Y
3 222 /peach /fruit /A2/ $2.00/ N
4 333/ pepper /veg /A3 /$3.00 /Y
5 444 /mushroom /veg/ A4 /$4.00 /N
6 555 /chicken /meat /B2 /$5.00 /Y
7 666/ ham /meat /B2/ $6.00 /N
8 777 /pasta /grain /E5/ $7.00/ Y
9 888 /bread/ grain /F7/ $8.00 /N
10 999 /milk /dairy /G2/ $9.00 /Y
11 1010 /cheese /dairy/ G2 /$10.00/ N

SHEET 2: Search
___A B
1 Lookup
2 name
3 type
4 location
5 price
6 sale

 

[c_m_s_jr]

Take a look at using advanced filter and the "copy to a different location" abilities. Should give you what you want.

 

[Rick Rothstein]

Assuming your data is on "SHEET 1" (note the space between the "T" and the "1"), then this Change event code (place in the "SHEET 2" code module) should do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "B1" Then
    On Error Resume Next
    With ActiveSheet
      .Range("B1").Resize(6) = Application.Transpose(Worksheets("SHEET 1").Columns("A").Find(.Range("B1").Value, .Cells(Rows.Count, "A"), xlValues, xlWhole, , xlNext, False).Resize(, 6))
      If Err.Number Then
        .Range("B2").Resize(5).Clear
        MsgBox "No such item!", vbCritical
      End If
    End With
  End If
End Sub

 

[h.j.barton]

Thanks. It works great!! The only issue is that my formatting (font size, cell colour, and conditional formating rules) are erased each time an incorrect item number is entered. Is there a way to fix this?

 

[Rick Rothstein]

Thanks. It works great!! The only issue is that my formatting (font size, cell colour, and conditional formating rules) are erased each time an incorrect item number is entered. Is there a way to fix this?

Okay, try changing this line of code...

.Range("B2").Resize(5).Clear

to this and see if that works better for you...

.Range("B2").Resize(5).ClearContents

 

[h.j.barton]

Perfect! Thank you so much!