Copying VLOOKUP

G

Guest

I'm working on a two worksheets.
The formula is as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BE;57;0)

I want to copy the formula to one cell on the right and turns as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BF;57;0)

My problem is that the coll index number does not change...

that means I have to change it manually for 50 collumns.

PLEASE HELP
 
D

Dave Peterson

=VLOOKUP($A92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$A:BE;
column('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!be:be);
0)

this will react to insertion/deletion of columns in that price list worksheet.

But you may want to consider using =index(match()).

=index('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!BE:BE;
match($a92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$a:$a;0)

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))

ps. watch out for typos. I use commas for my list separator. I tried to
change them to semicolons, but may have missed some.


I.C.E. said:
I'm working on a two worksheets.
The formula is as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BE;57;0)

I want to copy the formula to one cell on the right and turns as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BF;57;0)

My problem is that the coll index number does not change...

that means I have to change it manually for 50 collumns.

PLEASE HELP
Click to expand...
 
G

Guest

sorry Dave,

but it doesn't work either of the two ways.

In the first one, does not bring to the cell the right data.

Dave Peterson said:
=VLOOKUP($A92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$A:BE;
column('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!be:be);
0)

this will react to insertion/deletion of columns in that price list worksheet.

But you may want to consider using =index(match()).

=index('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!BE:BE;
match($a92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$a:$a;0)

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))

ps. watch out for typos. I use commas for my list separator. I tried to
change them to semicolons, but may have missed some.


I.C.E. said:
I'm working on a two worksheets.
The formula is as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BE;57;0)

I want to copy the formula to one cell on the right and turns as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BF;57;0)

My problem is that the coll index number does not change...

that means I have to change it manually for 50 collumns.

PLEASE HELP
Click to expand...
Click to expand...
 
D

Dave Peterson

When you look at the new formula (after copy|pasting), didn't that BE:BE change
to BF:BF?

In both formulas?????

Maybe you have calculation set to manual?

I.C.E. said:
sorry Dave,

but it doesn't work either of the two ways.

In the first one, does not bring to the cell the right data.

Dave Peterson said:
=VLOOKUP($A92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$A:BE;
column('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!be:be);
0)

this will react to insertion/deletion of columns in that price list worksheet.

But you may want to consider using =index(match()).

=index('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!BE:BE;
match($a92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$a:$a;0)

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))

ps. watch out for typos. I use commas for my list separator. I tried to
change them to semicolons, but may have missed some.


I.C.E. said:
I'm working on a two worksheets.
The formula is as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BE;57;0)

I want to copy the formula to one cell on the right and turns as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BF;57;0)

My problem is that the coll index number does not change...

that means I have to change it manually for 50 collumns.

PLEASE HELP
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ιt turns to BF:BF but it does not bring in the right data. Instead of the
content of the specific cells, it brings in the "lookup value".

Weird ha?

Dave Peterson said:
When you look at the new formula (after copy|pasting), didn't that BE:BE change
to BF:BF?

In both formulas?????

Maybe you have calculation set to manual?

I.C.E. said:
sorry Dave,

but it doesn't work either of the two ways.

In the first one, does not bring to the cell the right data.

Dave Peterson said:
=VLOOKUP($A92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$A:BE;
column('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!be:be);
0)

this will react to insertion/deletion of columns in that price list worksheet.

But you may want to consider using =index(match()).

=index('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!BE:BE;
match($a92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$a:$a;0)

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))

ps. watch out for typos. I use commas for my list separator. I tried to
change them to semicolons, but may have missed some.


I.C.E. wrote:

I'm working on a two worksheets.
The formula is as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BE;57;0)

I want to copy the formula to one cell on the right and turns as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BF;57;0)

My problem is that the coll index number does not change...

that means I have to change it manually for 50 collumns.

PLEASE HELP
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

Sorry, I don't have another guess.



I.C.E. said:
Ιt turns to BF:BF but it does not bring in the right data. Instead of the
content of the specific cells, it brings in the "lookup value".

Weird ha?

Dave Peterson said:
When you look at the new formula (after copy|pasting), didn't that BE:BE change
to BF:BF?

In both formulas?????

Maybe you have calculation set to manual?

I.C.E. said:
sorry Dave,

but it doesn't work either of the two ways.

In the first one, does not bring to the cell the right data.

:

=VLOOKUP($A92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$A:BE;
column('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!be:be);
0)

this will react to insertion/deletion of columns in that price list worksheet.

But you may want to consider using =index(match()).

=index('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!BE:BE;
match($a92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$a:$a;0)

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))

ps. watch out for typos. I use commas for my list separator. I tried to
change them to semicolons, but may have missed some.


I.C.E. wrote:

I'm working on a two worksheets.
The formula is as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BE;57;0)

I want to copy the formula to one cell on the right and turns as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BF;57;0)

My problem is that the coll index number does not change...

that means I have to change it manually for 50 collumns.

PLEASE HELP
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

thanx anyway dave.
I'll post it again.

Dave Peterson said:
Sorry, I don't have another guess.



I.C.E. said:
ÃŽâ„¢t turns to BF:BF but it does not bring in the right data. Instead of the
content of the specific cells, it brings in the "lookup value".

Weird ha?

Dave Peterson said:
When you look at the new formula (after copy|pasting), didn't that BE:BE change
to BF:BF?

In both formulas?????

Maybe you have calculation set to manual?

I.C.E. wrote:

sorry Dave,

but it doesn't work either of the two ways.

In the first one, does not bring to the cell the right data.

:

=VLOOKUP($A92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$A:BE;
column('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!be:be);
0)

this will react to insertion/deletion of columns in that price list worksheet.

But you may want to consider using =index(match()).

=index('[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!BE:BE;
match($a92;
'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE LIST'!$a:$a;0)

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions03.html (for =index(match()))

ps. watch out for typos. I use commas for my list separator. I tried to
change them to semicolons, but may have missed some.


I.C.E. wrote:

I'm working on a two worksheets.
The formula is as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BE;57;0)

I want to copy the formula to one cell on the right and turns as follows:

=VLOOKUP($A92;'[Q306 MME Pricing Aug 06 CUSTOMER COPY v5 (2).xls]PRICE
LIST'!$A:BF;57;0)

My problem is that the coll index number does not change...

that means I have to change it manually for 50 collumns.

PLEASE HELP
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

PLEASE HELP - VLOOKUP COPY 3
PLEASE HELP - COPY VLOOKUP 6
VLOOKUP 2
Help: Need Excel formula to return correct price from price history table 6
Return a price based on a customer from a drop down list 7
Trouble with a VLOOKUP formula 9
How can I increase a cell reference in VLOOKUP by 11 when copying? 2
Vlookup issue 6

Top