Dave,

I'm not sure how you mean "share the orginal formula". If you mean with
user, it was shared in the sense that the cells were not 'hidden'. Part of
the idea of the workbook was for the user to see and learn from the formulas
- but I guess they're also learning how easy it is to crash the formulas as
well.

If you mean share here then I can give a brief example:
=counta(a10:a20) is in cell a1

Say a number is placed in each of a10 to a14, then a1 will show 5; but if
the numbers are cut & pasted into another worksheet, the formula in a1
changes to
=counta(a15:a20)

Darn!

Regards,

Tom

"Dave Peterson" wrote:

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
.