maintain absolute reference when inserting rows

[Kate]

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!

 

[AB]

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.

 

[AB]

My post seem to have disapeared, but anyways:

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

 

[Kate]

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