I have two worksheets and I can pull the ColB column of data over to where I
want it with vlookup but how can I then get columns C thru G over to my
worksheet? I am trying to pull forecasted prices for the same row of
information. Here is my current function formula: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,2,FALSE) It would be nice to have the 2
change to a three when I drag the formula to the right. And then drag again
and have the 3 change to a 4, etc. Or what is the answer. Thanks in
advance. The sheet I am pulling data from looks somewhat like this:

ColA ColB ColC ColD ColE ColF
ColG
Part Number Year1 Year2 Year3 Year4 Year5 Year6
XXXX1111 $1.00 $1.25 $1.50 $1.75 $2.00 $2.25

Replace the 2 with COLUMN() or Column()-n

If data from B (2) is going to column D(4) then use COLUMN()-2 i.e 4-2 =2.
just copy across.

HTH

"Walter" wrote:

I have two worksheets and I can pull the ColB column of data over to where I
want it with vlookup but how can I then get columns C thru G over to my
worksheet? I am trying to pull forecasted prices for the same row of
information. Here is my current function formula: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,2,FALSE) It would be nice to have the 2
change to a three when I drag the formula to the right. And then drag again
and have the 3 change to a 4, etc. Or what is the answer. Thanks in
advance. The sheet I am pulling data from looks somewhat like this:

ColA ColB ColC ColD ColE ColF
ColG
Part Number Year1 Year2 Year3 Year4 Year5 Year6
XXXX1111 $1.00 $1.25 $1.50 $1.75 $2.00 $2.25

When I do this: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,column(),FALSE) I get an error. Data from Col B thru ColG in the example below is being pulled over to another workbook and being placed in Col I thru Col N.

"Toppers" wrote:

Replace the 2 with COLUMN() or Column()-n

If data from B (2) is going to column D(4) then use COLUMN()-2 i.e 4-2 =2.
just copy across.

HTH

"Walter" wrote:

I have two worksheets and I can pull the ColB column of data over to where I
want it with vlookup but how can I then get columns C thru G over to my
worksheet? I am trying to pull forecasted prices for the same row of
information. Here is my current function formula: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,2,FALSE) It would be nice to have the 2
change to a three when I drag the formula to the right. And then drag again
and have the 3 change to a 4, etc. Or what is the answer. Thanks in
advance. The sheet I am pulling data from looks somewhat like this:

ColA ColB ColC ColD ColE ColF ColG
Part Number Year1 Year2 Year3 Year4 Year5 Year6
XXXX1111 $1.00 $1.25 $1.50 $1.75 $2.00 $2.25

try in column I:

=VLOOKUP(H2,'[- Master.xls]Pricing'!$A$2:$G$4611,column()-7,FALSE)

I=column 9
B=column 2
9-7=2

You get an error because it tries to retrieve data from column I in your
Master w/sheet when you only specified 7 columns.(A:G)

Read my first reply again!

[If data from B (2) is going to column D(4) then use COLUMN()-2 i.e 4-2
=2. ]



"Walter" wrote:

When I do this: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,column(),FALSE) I get an error. Data from Col B thru ColG in the example below is being pulled over to another workbook and being placed in Col I thru Col N.

"Toppers" wrote:

Replace the 2 with COLUMN() or Column()-n

If data from B (2) is going to column D(4) then use COLUMN()-2 i.e 4-2 =2.
just copy across.

HTH

"Walter" wrote:

I have two worksheets and I can pull the ColB column of data over to where I
want it with vlookup but how can I then get columns C thru G over to my
worksheet? I am trying to pull forecasted prices for the same row of
information. Here is my current function formula: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,2,FALSE) It would be nice to have the 2
change to a three when I drag the formula to the right. And then drag again
and have the 3 change to a 4, etc. Or what is the answer. Thanks in
advance. The sheet I am pulling data from looks somewhat like this:

ColA ColB ColC ColD ColE ColF ColG
Part Number Year1 Year2 Year3 Year4 Year5 Year6
XXXX1111 $1.00 $1.25 $1.50 $1.75 $2.00 $2.25

To avoid the confusion over which column the formula goes into, you
can use COLUMN(B1) for the first formula. Also, H2 needs to be fixed,
so I suggest using:

=VLOOKUP($H2,'[- Master.xls]Pricing'!$A$2:$G$4611,COLUMN(B2),0)

Put this in I2 and then copy across - the B2 becomes C2, D2, E2 etc,
which returns 2, 3, 4, 5 etc through the COLUMN function.

Hope this helps.

Pete


On Jul 2, 9:12 pm, Toppers wrote:
try in column I:

=VLOOKUP(H2,'[- Master.xls]Pricing'!$A$2:$G$4611,column()-7,FALSE)

I=column 9
B=column 2
9-7=2

You get an error because it tries to retrieve data from column I in your
Master w/sheet when you only specified 7 columns.(A:G)

Read my first reply again!

[If data from B (2) is going to column D(4) then use COLUMN()-2 i.e 4-2
=2. ]



"Walter" wrote:
When I do this: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,column(),FALSE) I get an error. Data from Col B thru ColG in the example below is being pulled over to another workbook and being placed in Col I thru Col N.

"Toppers" wrote:

Replace the 2 with COLUMN() or Column()-n

If data from B (2) is going to column D(4) then use COLUMN()-2 i.e 4-2 =2.
just copy across.

HTH

"Walter" wrote:

I have two worksheets and I can pull the ColB column of data over to where I
want it with vlookup but how can I then get columns C thru G over to my
worksheet? I am trying to pull forecasted prices for the same row of
information. Here is my current function formula: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,2,FALSE) It would be nice to have the 2
change to a three when I drag the formula to the right. And then drag again
and have the 3 change to a 4, etc. Or what is the answer. Thanks in
advance. The sheet I am pulling data from looks somewhat like this:

ColA ColB ColC ColD ColE ColF ColG
Part Number Year1 Year2 Year3 Year4 Year5 Year6
XXXX1111 $1.00 $1.25 $1.50 $1.75 $2.00 $2.25- Hide quoted text -

- Show quoted text -

Hi,

I am interested in this formula and have tried it but the part where you
type "COLUMN(B2)" just don't work.

Please demostrate again using my example below. I have 2 worksheets ie.
"master" and "copy". I am trying to do a vlookup in "copy" for column B to F.

Master
---------
Row 1 Column A Column B Column C Column D Column E Column F
Row 2 a love dislike gorgeous loser creation
Row 3 b hate scott heaven durian earth
Row 4 c sunshine angie duty rambutan sky
Row 5 d rain meng positive excellent loch
Row 6 e beauty byte negative good ness


Copy
-----
Row 1 Column A Column B Column C Column D Column E Column F
Row 2 a
Row 3 b
Row 4 c
Row 5 d
Row 6 e

So, how do I do just ONE vlookup in "copy" COLUMN B and drag the formula
across from column C to G?
My existing formula in column B is
=VLOOKUP(A2,[Book1]Sheet1!$A$1:$F$6,COLUMN(),FALSE) and it works for column B
but when I try dragging to column C to F, it just returns error.

Please advise. Thanks thanks!

Appreciate your patience!


"Pete_UK" wrote:

To avoid the confusion over which column the formula goes into, you
can use COLUMN(B1) for the first formula. Also, H2 needs to be fixed,
so I suggest using:

=VLOOKUP($H2,'[- Master.xls]Pricing'!$A$2:$G$4611,COLUMN(B2),0)

Put this in I2 and then copy across - the B2 becomes C2, D2, E2 etc,
which returns 2, 3, 4, 5 etc through the COLUMN function.

Hope this helps.

Pete


On Jul 2, 9:12 pm, Toppers wrote:
try in column I:

=VLOOKUP(H2,'[- Master.xls]Pricing'!$A$2:$G$4611,column()-7,FALSE)

I=column 9
B=column 2
9-7=2

You get an error because it tries to retrieve data from column I in your
Master w/sheet when you only specified 7 columns.(A:G)

Read my first reply again!

[If data from B (2) is going to column D(4) then use COLUMN()-2 i.e 4-2
=2. ]



"Walter" wrote:
When I do this: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,column(),FALSE) I get an error. Data from Col B thru ColG in the example below is being pulled over to another workbook and being placed in Col I thru Col N.

"Toppers" wrote:

Replace the 2 with COLUMN() or Column()-n

If data from B (2) is going to column D(4) then use COLUMN()-2 i.e 4-2 =2.
just copy across.

HTH

"Walter" wrote:

I have two worksheets and I can pull the ColB column of data over to where I
want it with vlookup but how can I then get columns C thru G over to my
worksheet? I am trying to pull forecasted prices for the same row of
information. Here is my current function formula: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,2,FALSE) It would be nice to have the 2
change to a three when I drag the formula to the right. And then drag again
and have the 3 change to a 4, etc. Or what is the answer. Thanks in
advance. The sheet I am pulling data from looks somewhat like this:

ColA ColB ColC ColD ColE ColF ColG
Part Number Year1 Year2 Year3 Year4 Year5 Year6
XXXX1111 $1.00 $1.25 $1.50 $1.75 $2.00 $2.25- Hide quoted text -

- Show quoted text -

Hi,

It's me Tweety. Sorry but I knew where my mistake is. I need to lock the
reference cell ie. $A2 and it works now!!

Thanks!
Tweety

"Tweetybird" wrote:

Hi,

I am interested in this formula and have tried it but the part where you
type "COLUMN(B2)" just don't work.

Please demostrate again using my example below. I have 2 worksheets ie.
"master" and "copy". I am trying to do a vlookup in "copy" for column B to F.

Master
---------
Row 1 Column A Column B Column C Column D Column E Column F
Row 2 a love dislike gorgeous loser creation
Row 3 b hate scott heaven durian earth
Row 4 c sunshine angie duty rambutan sky
Row 5 d rain meng positive excellent loch
Row 6 e beauty byte negative good ness


Copy
-----
Row 1 Column A Column B Column C Column D Column E Column F
Row 2 a
Row 3 b
Row 4 c
Row 5 d
Row 6 e

So, how do I do just ONE vlookup in "copy" COLUMN B and drag the formula
across from column C to G?
My existing formula in column B is
=VLOOKUP(A2,[Book1]Sheet1!$A$1:$F$6,COLUMN(),FALSE) and it works for column B
but when I try dragging to column C to F, it just returns error.

Please advise. Thanks thanks!

Appreciate your patience!


"Pete_UK" wrote:

To avoid the confusion over which column the formula goes into, you
can use COLUMN(B1) for the first formula. Also, H2 needs to be fixed,
so I suggest using:

=VLOOKUP($H2,'[- Master.xls]Pricing'!$A$2:$G$4611,COLUMN(B2),0)

Put this in I2 and then copy across - the B2 becomes C2, D2, E2 etc,
which returns 2, 3, 4, 5 etc through the COLUMN function.

Hope this helps.

Pete


On Jul 2, 9:12 pm, Toppers wrote:
try in column I:

=VLOOKUP(H2,'[- Master.xls]Pricing'!$A$2:$G$4611,column()-7,FALSE)

I=column 9
B=column 2
9-7=2

You get an error because it tries to retrieve data from column I in your
Master w/sheet when you only specified 7 columns.(A:G)

Read my first reply again!

[If data from B (2) is going to column D(4) then use COLUMN()-2 i.e 4-2
=2. ]



"Walter" wrote:
When I do this: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,column(),FALSE) I get an error. Data from Col B thru ColG in the example below is being pulled over to another workbook and being placed in Col I thru Col N.

"Toppers" wrote:

Replace the 2 with COLUMN() or Column()-n

If data from B (2) is going to column D(4) then use COLUMN()-2 i.e 4-2 =2.
just copy across.

HTH

"Walter" wrote:

I have two worksheets and I can pull the ColB column of data over to where I
want it with vlookup but how can I then get columns C thru G over to my
worksheet? I am trying to pull forecasted prices for the same row of
information. Here is my current function formula: =VLOOKUP(H2,'[-
Master.xls]Pricing'!$A$2:$G$4611,2,FALSE) It would be nice to have the 2
change to a three when I drag the formula to the right. And then drag again
and have the 3 change to a 4, etc. Or what is the answer. Thanks in
advance. The sheet I am pulling data from looks somewhat like this:

ColA ColB ColC ColD ColE ColF ColG
Part Number Year1 Year2 Year3 Year4 Year5 Year6
XXXX1111 $1.00 $1.25 $1.50 $1.75 $2.00 $2.25- Hide quoted text -

- Show quoted text -