Referencing the same cells
Two ways:
With INDIRECT you can be sure you always reference K7:BB48, even if
rows are added or deleted above row 7.
=INDEX(INDIRECT("'J:\KENTON\INTERMODAL\[DRAYMAN FUEL SURCHARGE
CHARTS.xls]COVER'!$K$7:$BB$48"),MATCH(X25,INDIRECT("'J:\KENTO N
\INTERMODAL\[DRAYMAN FUEL SURCHARGE CHARTS.xls]COVER'!$A$7:$A$48",
0),MATCH(AH10,'J:\KENTON\INTERMODAL\[DRAYMAN FUEL SURCHARGE CHARTS.xls]
COVER'!$K$6:$BB$6,0))
With OFFSET you can be sure you always reference 42 rows, starting
from row 7 but if rows are added or deleted above it will still work.
=INDEX(OFFSET('J:\KENTON\INTERMODAL\[DRAYMAN FUEL SURCHARGE CHARTS.xls]
COVER'!$K$7,0,0,42,COLUMNS($K:$BB)),MATCH(X25,OFFS ET('J:\KENTON
\INTERMODAL\[DRAYMAN FUEL SURCHARGE CHARTS.xls]COVER'!$A$7,0,0,42,1),
0),MATCH(AH10,'J:\KENTON\INTERMODAL\[DRAYMAN FUEL SURCHARGE CHARTS.xls]
COVER'!$K$6:$BB$6,0))
Of course you can combine both.
HTH
Kostis Vezerides
On Nov 25, 5:51*pm, Kenton wrote:
I need some help changing the following formula to reference the same cells
once the reference sheet is updated (additional rows are added). The formula
is as follows...
=INDEX('J:\KENTON\INTERMODAL\[DRAYMAN FUEL SURCHARGE
CHARTS.xls]COVER'!$K$7:$BB$48,MATCH(X25,'J:\KENTON\INTERMODAL \[DRAYMAN FUEL
SURCHARGE
CHARTS.xls]COVER'!$A$7:$A$48,0),MATCH(AH10,'J:\KENTON\INTERMO DAL\[DRAYMAN
FUEL SURCHARGE CHARTS.xls]COVER'!$K$6:$BB$6,0))
... and I need the following cells to stay the same when additional rows are
added to the reference sheet. $K$7:$BB$48 and $A$7:$A$48. Thanks.
|