Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup
I have a workbook and on sheet 1 I have entered information in columns A thru
R. I am needing a formula that will populate sheet 2 when I enter an identifying number. Example Sheet 1 ID#, Address, Date, Phone, etc. So when I enter the ID# on Sheet 2 all the info in column B thru R is populated in sheet 2. Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup
you can use VLOOKUP()... =VLOOKUP($A2,Sheet1!A:R,2,0) would go into cell B2
change to =VLOOKUP($A2,Sheet1!A:R,3,0) in C2 and so on. Alternately, you can look at perhaps some VB code to keep your formulas from taking up a huge amount of space. Unfortunately, I'm no good with writing that kind of thing... "Trev" wrote: I have a workbook and on sheet 1 I have entered information in columns A thru R. I am needing a formula that will populate sheet 2 when I enter an identifying number. Example Sheet 1 ID#, Address, Date, Phone, etc. So when I enter the ID# on Sheet 2 all the info in column B thru R is populated in sheet 2. Thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup
Is there a way for the #N/A to not show in empty rows?
Thanks "Sean Timmons" wrote: you can use VLOOKUP()... =VLOOKUP($A2,Sheet1!A:R,2,0) would go into cell B2 change to =VLOOKUP($A2,Sheet1!A:R,3,0) in C2 and so on. Alternately, you can look at perhaps some VB code to keep your formulas from taking up a huge amount of space. Unfortunately, I'm no good with writing that kind of thing... "Trev" wrote: I have a workbook and on sheet 1 I have entered information in columns A thru R. I am needing a formula that will populate sheet 2 when I enter an identifying number. Example Sheet 1 ID#, Address, Date, Phone, etc. So when I enter the ID# on Sheet 2 all the info in column B thru R is populated in sheet 2. Thanks, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
lookup
Expanding on Sean's vlookup suggestion, perhaps try this.
On sheet two select B through R on the row you want the data to show up on. While selected, enter this formula in the 'white' cell. (all on one line in the formula box, it seems to have wrapped here) =VLOOKUP(A1,Sheet1!A1:R3,{2,3,4,5,6,7,8,9,10,11,12 ,13,14,15,16,17,18},0) Now enter using CTRL + SHIFT + ENTER. You will get the curly brackets around the formula... { }. So you have your ID #'s in column A of sheet 1 and the data for each number in the same row from B to R,. Your lookup formulas are Array Entered on sheet 2, B to R, enter the ID# in A1. Returns 17 items of information for that ID#. If you need to change the formula to accommodate changes in your data or data format, (add data or subtract a row or the such from sheet 1) you will need to select the entire sheet 2 B to R row, make your changes and the re-enter with CTRL + SHIFT + ENTER. HTH Regards, Howard "Trev" wrote in message ... I have a workbook and on sheet 1 I have entered information in columns A thru R. I am needing a formula that will populate sheet 2 when I enter an identifying number. Example Sheet 1 ID#, Address, Date, Phone, etc. So when I enter the ID# on Sheet 2 all the info in column B thru R is populated in sheet 2. Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Matrix lookup/mulitple criteria lookup | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) |