With a bit more experiment I've been able to recreate the reported errors
(the corrupted formulas) by cutting and pasting the entry data to particular
locations - darn it.

Question of course now is, is there any way with explicit formulas (i.e. not
using VBA) to prevent this type of formula corruption from cut & paste? - I
thought worksheet protection and careful setting of the user rights was going
to be enough but clearly it isn't.


"Tom-S" wrote:

Dave, Jim

Thanks for your responses.

To give 2 examples of formula corruption I've seen (so far):

1) =COUNTA(range), the range was different to the one orginally set.

2) #REF! replacing relative cell refs within a formula.

Jim, I know you've just mentioned the #REF! type, but when I cut and pasted
a data entry to another cell then the formula adopted the new cell ref, which
is still wrong, but it didn't convert to #REF!

Deleting cells is not permitted within the protection rights that I set on
the worksheets.

Regards,

Tom

"Jim Thomlinson" wrote:

If you cut or delete any of the cells which are referenced in the formula
then the link to that cell will become #REF...
--
HTH...

Jim Thomlinson


"Tom-S" wrote:

Is there any way (in Excel 2003) in which a formula in a locked cell of a
password protected worksheet can end up corrupted, without the sheet having
first been unprotected?

I recently created a workbook with password protected worksheets, which
accepts data entry in some unlocked cells and carries out data analysis by
formulas in locked cells. There are no macros in the workbook.

I was asked to investigate when the analysis stopped functioning correctly,
and I found some of the formulas in the locked cells had become corrupted,
but I have no idea how this could have happened - without somebody hacking
the worksheet password. Before I investigate a possible hack I just need to
know if there are any other ways the formula corruption could have occurred
without a password hack.

Any help gladly appreciated.