Thanks Dave. I want to 'fix' the formula by not allowing it to be altered in
any way i.e. any ranges referred to must stay as originally written, so I
guess indirect() is the way to go. Funnily enough, one of the formulas that
got corrupted already had an indirect() as part of the formula - so was a
part which didn't get corrupted - but I didn't figure on needing it for every
cell ref. Dream on!

Regards,

Tom


"Dave Peterson" wrote:

If you always want the formula to point at a1:a10, you could use:

=sum(indirect("a1:a10"))

You can delete/move/destroy the rows/columns/cells and the formula will still
point at A1:A10.

But I don't know how you want to fix the formula. If you want to point at the
original range and the range where a portion of the cells were cut and pasted,
then I think you're out of luck.

I would treat this as a training issue. Make sure that the users are aware what
happens when they cut and paste.

Tom-S wrote:

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
.


--

Dave Peterson
.