#Ref! error - how to solve

[DavidAustin]

Hi all,

I know that a #Ref! error occurs when the cell that is being referenced in a formula is no longer valid (e.g. deleted)

I am trying to make a spreadsheet for my colleague where every month she copies in data from a monthly generated report and the spreadsheet takes the data and pulls out specific information. However, every month she comes back and tells me there's loads of #Ref! errors because when she copies in/deletes some rows of the data, the existing cells no longer exist.

Does anyone know how I can stop this from happening?

[Luuk]

Make sure she cannot delete cells that are used in functions

This sounds harder than it is. If all data dat is changed is in 'Sheet1', and your calculations are in 'Sheet2', it should not be possible to get a '#Ref!'

Give her a course in Excel

I hope I do not have to explain this.... ;)