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,

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,

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,

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,