| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Referencing the same cells
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing Cells | Excel Worksheet Functions | |||
| Referencing Cells | Excel Worksheet Functions | |||
| Referencing other cells | Excel Discussion (Misc queries) | |||
| Referencing cells | New Users to Excel | |||
| Referencing Cells | Excel Worksheet Functions | |||
Linear Mode
