Referencing the same cells

  • Thread starter Thread starter Kenton
  • Start date Start date
K

Kenton

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\INTERMODAL\[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.
 
Kenton,

You can use the indirect formula to indicate a range that will never change,
even when the rows or columns shift.
 
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:\KENTON
\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,OFFSET('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
 
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 said:
where you add the row eg. after cell a or after cell b

Kenton said:
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\INTERMODAL\[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.
Click to expand...
Click to expand...
 
I am aware of the indirect formula but I am having trouble incorporating it
into the index formula. Thanks though.

galimi said:
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
(e-mail address removed)
888-MY-ETHER ext. 01781474



Kenton said:
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\INTERMODAL\[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.
Click to expand...
Click to expand...
 
Perfect. Thanks so much.

vezerid said:
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:\KENTON
\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,OFFSET('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


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\INTERMODAL\[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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add Cells error 2
Repeat formula within a cell 5
Referencing the same cell on multiple sheets 1
Linking different cells from one sheet to another in same workbook 2
Excel macro code for copying data to a cell reference which is set byvalues in two separate cells in 3
Sumproduct where cell values are the same in a sorted column 1
How do I reference the same cell in all previous sheets? 3
Formula Help within Code 1

Back
Top