Adjacent formual stop working when I delet a range

[berryalan]

Hi All,

I have a worksheet in which users populate action points in columns A:G. Column G has a completion date for the task. In column H I have an if statement that checks whether the action is late or not depending on today's date and the closure date in column G. All works fine.

At the end of the week the user wants to click on a button which removes all closed actions and copies them to the appropriate team leaders sheet. Again I have managed to get this working, but the stumbling block is that once the range of "completed" actions is removed, the formula in column H start misbehaving, presumably because the data they were referring to has gone.

Any tips in preventing this happening would be gratefully recieved.

Regards

Alan
Scotland

 

[Andrew Poulsom]

To avoid the #REF! error clear the contents of the cells rather than deleting them.

 

[berryalan]

To avoid the #REF! error clear the contents of the cells rather than deleting them.

Hi Andrew,

Thanks for the response. Once the closed actions are moved over to the team leaders' sheets, I want to delete them so that the ones remaining are at the top of the table.

I have worked around it by entering the formula in H1 and once the delete has been actioned, coded an auto-fill down 20 rows. Not ideal as I am sure we'll have a call to the helpdesk in a few weeks when action 21 isn't reported properly.

Thanks

Alan