Copying VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
=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...
 
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...
 
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...
 
Ι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...
 
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...
 
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...
 
Back
Top