|
Referencing the same cells
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. |
Referencing the same cells
where you add the row eg. after cell a or after cell b
"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. |
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. |
Referencing the same cells
Not sure of your question. On the reference sheet, rows 7 through 13 are
copied and inserted. In the equation, those cells change from $A$7 to $A$14 (48's change to 55's) but I need them to stay the same. I am aware of the indirect equation but can not seem to incorporate it into the index lookup with the match. Thanks again. "Sanjay" wrote: where you add the row eg. after cell a or after cell b "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. |
Referencing the same cells
I am aware of the indirect formula but I am having trouble incorporating it
into the index formula. Thanks though. "galimi" wrote: Kenton, You can use the indirect formula to indicate a range that will never change, even when the rows or columns shift. -- http://www.ExcelHelp.us 888-MY-ETHER ext. 01781474 "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. |
Referencing the same cells
Perfect. Thanks so much.
"vezerid" wrote: 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. |
| All times are GMT +1. The time now is 07:54 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com