I have a workbook with two worksheets.
One one sheet a part number will be scaned and entered into col A.
The other worksheet will have a existing list, in col A the part numbers and
col B thru F will have other information that goes with the part number.

When the user enters the part number ( using a scanner) it will appear in
col A of the first worksheet, I want the other information ( in worksheet 2 )
matching that part number to appear in col B thru F in worksheet 1.

How do I make col A search for the row with the same part number in
worksheet 2 and bring the data over to worksheet 1 in col B thru F?
Thanks,
Barbara

B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)

copy across and down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Barbara" wrote in message
...
I have a workbook with two worksheets.
One one sheet a part number will be scaned and entered into col A.
The other worksheet will have a existing list, in col A the part numbers
and
col B thru F will have other information that goes with the part number.

When the user enters the part number ( using a scanner) it will appear in
col A of the first worksheet, I want the other information ( in worksheet
2 )
matching that part number to appear in col B thru F in worksheet 1.

How do I make col A search for the row with the same part number in
worksheet 2 and bring the data over to worksheet 1 in col B thru F?
Thanks,
Barbara

It's working!
One more question, How do I copy without having to go in and change the
first cell referance back to A1 in each cells formula?
Barbara

"Bob Phillips" wrote:

B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)

copy across and down

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Barbara" wrote in message
...
I have a workbook with two worksheets.
One one sheet a part number will be scaned and entered into col A.
The other worksheet will have a existing list, in col A the part numbers
and
col B thru F will have other information that goes with the part number.

When the user enters the part number ( using a scanner) it will appear in
col A of the first worksheet, I want the other information ( in worksheet
2 )
matching that part number to appear in col B thru F in worksheet 1.

How do I make col A search for the row with the same part number in
worksheet 2 and bring the data over to worksheet 1 in col B thru F?
Thanks,
Barbara

Barbara,

=VLOOKUP($A1,Sheet2!$A$1:$F$100,Column(),False)
or
=VLOOKUP($A$1,Sheet2!$A$1:$F$100,Column(),False)

HTH,
Bernie
MS Excel MVP


"Barbara" wrote in message
...
It's working!
One more question, How do I copy without having to go in and change the
first cell referance back to A1 in each cells formula?
Barbara

I'd probably use VLOOKUP() instead.


Barbara Wrote:
I have a workbook with two worksheets.
One one sheet a part number will be scaned and entered into col A.
The other worksheet will have a existing list, in col A the part
numbers and
col B thru F will have other information that goes with the part
number.

When the user enters the part number ( using a scanner) it will appear
in
col A of the first worksheet, I want the other information ( in
worksheet 2 )
matching that part number to appear in col B thru F in worksheet 1.

How do I make col A search for the row with the same part number in
worksheet 2 and bring the data over to worksheet 1 in col B thru F?
Thanks,
Barbara


--
Morrigan
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=391033

Yes the VLookup is working nicely. Thanks
One question though, I understand what everything is doing in the formula
except Column()
Is that giving it a variable for whatever is in the column to copy over?
Barbara

Hi Barbara
Column() returns the column number A=1 B=2 etc.
In your formula, the use of COLUMN() is automatically stepping up the offset
argument in the Vlookup expression to choose the appropriate value from the
reference table.

--
Regards
Roger Govier
"Barbara" wrote in message
...
Yes the VLookup is working nicely. Thanks
One question though, I understand what everything is doing in the formula
except Column()
Is that giving it a variable for whatever is in the column to copy over?
Barbara

Ok, what if I change a couple of things.
I need to add in four columns (a new A thru D) on Sheet 1
And the info I am now getting on Sheet 2 is in A2 through I13

So the formula is in cell F2 thru M2 in Sheet 1(and copied down)
And it is reading from the data entered into column E in sheet 1 (E in sheet
one searchs column A in sheet 2 to get the data and bring back to sheet 1 and
put it in columns F thru L )

Since Column reads A=1, B=2 and so on, is there a way to change it to be
column F thru L.

I hope I am making since.
Barbara
"Roger Govier" wrote:

Hi Barbara
Column() returns the column number A=1 B=2 etc.
In your formula, the use of COLUMN() is automatically stepping up the offset
argument in the Vlookup expression to choose the appropriate value from the
reference table.

--
Regards
Roger Govier
"Barbara" wrote in message
...
Yes the VLookup is working nicely. Thanks
One question though, I understand what everything is doing in the formula
except Column()
Is that giving it a variable for whatever is in the column to copy over?
Barbara

Hi Barbara

In any formula you can either add to or subtract from column number so
COLUMN()+4 where column =2 would refer to an offset of 6 from the value
found in VLOOKUP, rather than an offset of 2.

The originla formula given to you by Bob to place in Sheet 1 was
B1: =VLOOKUP(A1,Sheet2!$A$1:$F$100,Column(),False)

If you have inserted 4 columns on Sheet 1 then the formula will now be in F1
and would read
=VLOOKUP(E1,Sheet2!$A$1:$F$100,Column(),False)
Because nothing has altered on Sheet2, then it will be reading values 4
columns further over on Sheet2 than it should, so in your case we need to
subtract from COLUMN().

If I have understood you correctly, then I believe the formula should now be
F1: =VLOOKUP(E1,Sheet2!$A$1:$F$100,Column()-4,False)

--
Regards
Roger Govier
"Barbara" wrote in message
...
Ok, what if I change a couple of things.
I need to add in four columns (a new A thru D) on Sheet 1
And the info I am now getting on Sheet 2 is in A2 through I13

So the formula is in cell F2 thru M2 in Sheet 1(and copied down)
And it is reading from the data entered into column E in sheet 1 (E in
sheet
one searchs column A in sheet 2 to get the data and bring back to sheet 1
and
put it in columns F thru L )

Since Column reads A=1, B=2 and so on, is there a way to change it to be
column F thru L.

I hope I am making since.
Barbara
"Roger Govier" wrote:

Hi Barbara
Column() returns the column number A=1 B=2 etc.
In your formula, the use of COLUMN() is automatically stepping up the
offset
argument in the Vlookup expression to choose the appropriate value from
the
reference table.

--
Regards
Roger Govier
"Barbara" wrote in message
...
Yes the VLookup is working nicely. Thanks
One question though, I understand what everything is doing in the
formula
except Column()
Is that giving it a variable for whatever is in the column to copy
over?
Barbara