Retrieving Data from another sheet

[Mike B]

Hello, I am trying to figure out a way to make a database and I was hoping
someone could help me with a snag I hit. I am trying to create a database in
Excel. It will basically have two sheets.

One for all of the information, and the other to act as a sort of search
function. The sheet with all of the information will have several columns,
including a persons name, clothing size, shoe size, etc. What I want on the
second sheet it to be able to type the persons name (which will be column A)
and be able to search the database and show all of the information in the
rest of the columns. (For example, the name in column A2 is Joe and there are
three other columns. The search would return all of the information in
columns B2, C2 and D2)

I know that there is a way to do this, but my experience in programing with
Excel is somewhat limited. Is there anyone that can help or at least point me
in the right direction? Any help would be greatly appreciated. Thank you.

 

[JLGWhiz]

Here is one way. The code goes in the sheet code module for sheet2. The
macro executes when a name is typed into cell A2 of sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("A2") Then
Set c = Sheets("Sheet1").Cells _
.Find(Target.Value, LookIn:=xlValues)
If Not c Is Nothing Then
MsgBox c.Value & " " & c.Offset(0, 1).Value & " " _
& c.Offset(0, 2).Value & " " & c.Offset(0, 3).Value
End If
End If
End Sub

If you want the data to appear on sheet two. Then substitute a copy
statement for the message box.

Sheets(1).Range("B" & c.Row & "" & c.Row).Copy Sheets(2).Range("B2")

 

[JLGWhiz]

To avoid a type mismatch error, change this line:

If Target = Range("A2") Then

To this:

If Target.Address = Range("A2").Address Then

 

[Mike B]

Awesome, thanks a bunch. One last question, if I want to add more columns to
be shown, how should I modify the code (ex: instead of columns b2, c2, d2, i
want to pull through g2)? Thanks.