I have created a sheet which allows lab staff to enter data (Columns A to H) which I then pivot into reports using the results in Colums L to Q. I have realised that if the guys cut lines of data and paste them elsewhere that the calculations stay in the original lines. And the new lines insert !ref into the calculations. When this sheet is finished the calculation cells would normally be locked and the sheet protected to stop them accidently deleting formulae but this seems to happen automatically.
I checked the forum and thought that by making the calculation use absolute references that my problem would be solved.(sample attached) but it isn't. IN this example I have cut cells A9:F9 and pasted them in A24:F24
Copying and pasting data and then clearing the orignal cells it works fine but I know at some time in the future someone will either cut or insert which will render all the calculations worthless.
How do I stop this happening?
Dr M
Bookmarks