Is there a better formula than this Q

[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

 

[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
|

 

[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

 

[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 -

Thanks guys

 

[Pete_UK]

You're welcome.

Pete

Thanks guys- Hide quoted text -

- Show quoted text -