Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving Data from another sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving Data from another sheet
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 & ":D" & c.Row).Copy Sheets(2).Range("B2") "Mike B" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving Data from another sheet
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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Retrieving Data from another sheet
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. "JLGWhiz" wrote: 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro for retrieving a set of data from excel sheet | Excel Discussion (Misc queries) | |||
Retrieving Data: Speed of beating down rows vs retrieving from array? | Excel Programming | |||
Need help searching another sheet and retrieving a value | Excel Worksheet Functions | |||
retrieving values from a specified sheet | Excel Worksheet Functions | |||
Retrieving data from work sheet to USER FORM | Excel Programming |