Absolute moving

[AquaSky]

Hi hope you can help.

I am working on a spreadsheet that is to help children track paper waste within a school. I have a macro to save the record and that seems to be working fine.

The issue I am having is the the absolute bell I am using as a reference, I want the previous record to be looked up so it can work out the percentage increase or decrease or paper waste.

At the moment I have an absolute cell to copy the last entry from a table on another sheet, E.g. =records!$B$3. However when I run the macro that copies the newest record into the table and adds a new row the reference changes to $B$4.

Am I doing something wrong? How can I solve the reference issue.

Thanks in advance

 

[davesexcel]

What's the code?

 

[AquaSky]

What do you mean by code? For the macro?

The macro was a recorded macro where a row is inserted to the table and the information of copied from the calculations sheet to the record sheet.

 

[davesexcel]

Ah, so the insert changes the cell reference?

 

[AquaSky]

Yeah I’m not sure how to get around it

 

[AquaSky]

Managed to solve it by using a helper cell and indirect function instead of absolute. Hope that helps anyone else who struggles with this issue.