Insert row without adjusting references
 

I have a table containing multiple rows. I've got a second table containing
cell references to the first table. (So row 1 of the second table refers to
row 1 of the first table, row 2 refers to row 2, etc.)

Normally if I insert a row in the first table, Excel automatically adjusts
the references in the second table so that they point to the same cells, even
though those cells have now now moved. I'm looking for a way to add rows to
the first table *without* having Excel automatically adjust the references in
the second table. (So if I insert row 3 in the first table, now row 1 refers
to row 1, row 2 refers to row 2, row 3 refers to row 3 (not row 4), etc.)

Any suggestions? Thanks.

Insert row without adjusting references
 

try using the indirect() function and using row()
for example
=indirect("Sheet1!B"&row()) will always refer to the same row the equation
is in

"Eric" wrote:

I have a table containing multiple rows. I've got a second table containing
cell references to the first table. (So row 1 of the second table refers to
row 1 of the first table, row 2 refers to row 2, etc.)

Normally if I insert a row in the first table, Excel automatically adjusts
the references in the second table so that they point to the same cells, even
though those cells have now now moved. I'm looking for a way to add rows to
the first table *without* having Excel automatically adjust the references in
the second table. (So if I insert row 3 in the first table, now row 1 refers
to row 1, row 2 refers to row 2, row 3 refers to row 3 (not row 4), etc.)

Any suggestions? Thanks.

Insert row without adjusting references
 

Use a 'cheater' column with =Row()-1 in it.
Then use the Offset(reference,row,col) function.
'reference' will point to the first row of the first table
'row' will reference the 'cheater' column
col will be -0- (zero).

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Eric" wrote:

I have a table containing multiple rows. I've got a second table containing
cell references to the first table. (So row 1 of the second table refers to
row 1 of the first table, row 2 refers to row 2, etc.)

Normally if I insert a row in the first table, Excel automatically adjusts
the references in the second table so that they point to the same cells, even
though those cells have now now moved. I'm looking for a way to add rows to
the first table *without* having Excel automatically adjust the references in
the second table. (So if I insert row 3 in the first table, now row 1 refers
to row 1, row 2 refers to row 2, row 3 refers to row 3 (not row 4), etc.)

Any suggestions? Thanks.