I think you'll have to share the original formula, too.

And any details that happened to the range referred to in that original formula.

If you used:
=counta(Sheet99!a:a)
and sheet99 was deleted, you'll get the error.

And worksheet protection won't stop this kind of thing.


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.

--

Dave Peterson