maintain absolute reference when inserting rows
 

I have 2 worksheets within a spreadsheet - one is the data, one the report. I
have a macro that inserts a new row on the data table at row 5. I always want
my report to reflect the data in row 5. I used an absolute reference for R5C1
in my report but every I insert a new row on the data table, the reference
moves to R6C1.

Can anyone help? Thanks!

maintain absolute reference when inserting rows
 

On 17 July, 20:18, Kate wrote:
I have 2 worksheets within a spreadsheet - one is the data, one the report. I
have a macro that inserts a new row on the data table at row 5. I always want
my report to reflect the data in row 5. I used an absolute reference for R5C1
in my report but every I insert a new row on the data table, the reference
moves to R6C1.

Can anyone help? Thanks!

Instead of =R5C1 use this:
=INDIRECT("R5C1";FALSE)
because "R5C1" is a string here, no insertions/delitions would change
that.

maintain absolute reference when inserting rows
 

My post seem to have disapeared, but anyways:

instead of reference
=R5C1
use reference:
=INDIRECT("R5C1";FALSE)


On 17 July, 20:18, Kate wrote:
I have 2 worksheets within a spreadsheet - one is the data, one the report. I
have a macro that inserts a new row on the data table at row 5. I always want
my report to reflect the data in row 5. I used an absolute reference for R5C1
in my report but every I insert a new row on the data table, the reference
moves to R6C1.

Can anyone help? Thanks!

maintain absolute reference when inserting rows
 

After many trials and errors, I got it to work. Many thanks!!

"AB" wrote:

My post seem to have disapeared, but anyways:

instead of reference
=R5C1
use reference:
=INDIRECT("R5C1";FALSE)


On 17 July, 20:18, Kate wrote:
I have 2 worksheets within a spreadsheet - one is the data, one the report. I
have a macro that inserts a new row on the data table at row 5. I always want
my report to reflect the data in row 5. I used an absolute reference for R5C1
in my report but every I insert a new row on the data table, the reference
moves to R6C1.

Can anyone help? Thanks!