Is there a better formula than this Q

S

Sean

I want to effect the following formula

=VLOOKUP((SheetA!B5-364),Data!A77:AD128,4)

But I wish to drag this accross 30 other columns, how would I
construct a fomula that would not require me to change the last digt
from 4 to 5; 6; 7; 8... etc 30 times?


Thanks
 
N

Niek Otten

Instead of the 4, use COLUMN() plus or minus something. For example, if the formula is in Column F, use COLUMN()-2 to get 4.


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I want to effect the following formula
|
| =VLOOKUP((SheetA!B5-364),Data!A77:AD128,4)
|
| But I wish to drag this accross 30 other columns, how would I
| construct a fomula that would not require me to change the last digt
| from 4 to 5; 6; 7; 8... etc 30 times?
|
|
| Thanks
|
 
P

Pete_UK

Change it to this:

=VLOOKUP((SheetA!$B5-364),Data!$A77:$AD128,COLUMN(D1))

and then copy it across.

COLUMN(D1) returns 4 - when copied into the next column it becomes
COLUMN(E1) which returns 5, etc. Note also the use of the $ symbols so
the cell references don't change when you copy it across.

Hope this helps.

Pete
 
S

Sean

Change it to this:

=VLOOKUP((SheetA!$B5-364),Data!$A77:$AD128,COLUMN(D1))

and then copy it across.

COLUMN(D1) returns 4 - when copied into the next column it becomes
COLUMN(E1) which returns 5, etc. Note also the use of the $ symbols so
the cell references don't change when you copy it across.

Hope this helps.

Pete




- Show quoted text -
Click to expand...

Thanks guys
 
Top