corrupted formula in protected worksheet

[Tom-S]

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]

I've seen workbooks that get corrupted -- so that they won't open (or won't open
sometimes).

But I'm not sure what corrupted formulas are.

Can you explain in more detail?

Maybe it'll help someone else who's had this issue give you a good answer.

 

[Jim Thomlinson]

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

 

[Tom-S]

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

 

[Dave Peterson]

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]

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.

 

[Jim Thomlinson]

Not what you want to hear but the answer is no.

 

[Tom-S]

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]

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]

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]

Be careful.

If you as the designer make changes to the workbook, you may find that your
formulas don't do what you want.

For instance, say you have that
=sum(indirect("a1:a10"))
in a cell

You decide to add some notes and instructions to that sheet. So you add a few
new rows (new rows 1:4).

The formula still points at A1:A10 -- it includes those 4 rows of headers.

And if you decided you needed a new cell and inserted a new row 7, then the
formula didn't adjust to include that new cell.

You'll want to verify all the formulas that you use this way.

And be aware that =indirect() is a volatile function. That means that formulas
that use this will recalculate each and everytime excel recalcs.

If you have lots of them, you may find that those recalcs take more time than
before.

I'd use this syntax sparingly.

 

[Tom-S]

Thanks for the note Dave. I wouldn't be making any changes to the
architecture of the worksheets, which everyone's quite happy with - it's
purely the corruption of the formulas that's been problematic. Also there
aren't a huge amount of formulas in these workbooks so I don't think the
re-calc time is an issue in this particular case. What is unfortunate is that
I'll have to individually create each indirect() part of each formula instead
of drag filling as before with the relative cell refs. That's life.

Regards,

Tom

 

[Tom-S]

Supplemental related question:

I've been using named ranges in formulas and already noted that if cut &
paste is used on a named range that the name travels to the pasted cell and
formulas dependent on that name adopt the value in the pasted cell. So, is
there a way of protecting named ranges from this loss caused by cut & paste
i.e to make a name always refer to the same range?

I've already tried using =INDIRECT("Sheet1!A1") in the 'Refers to' box of
the Define Name dialogue, and have seen that it preserves the target cell ref
for the name if the contents of A1 are cut & pasted elsewhere.

However, (1) the name of the named range no longer shows up in the worksheet
name box, and, more importantly for my formulas, (2) the name that I create
in the Define Name dialogue can no longer be referenced from another INDIRECT
call up.

e.g. say I have cells A1, C1, E1 as named ranges for "Max_Day_1",
"Max_Day_2", and "Max_Day_3", and cell D4 contains either 1, 2 or 3, then
previously my formula in E4 =INDIRECT("Max_Day_" & D4) was working ok, but if
INDIRECT is used in the 'Refers to' of Define Name then this formula in E4
crashes, yet I need to include a variable within the name call up. So, any
help with how these 2 things together can be achieved?

Regards,

Tom

 

[Dave Peterson]

I don't understand what "this formula in E4 crashes" means.

It could be as simple as using:
=INDIRECT("Max_Day_" & Sheet1!$D$4)


And if you're working with names, I'd get this, too:
Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name Manager:
NameManager.Zip from http://www.oaltd.co.uk/mvp

 

[Tom-S]

Hi Dave. What I mean is that the formula (in E4) =INDIRECT("Max_Day_" & D4)
works ok when the 'Refers to' box of the Define Name dialogue does not employ
INDIRECT, but it doesn't work ok & returns a #REF! error when 'Refers to'
does employ INDIRECT.

=INDIRECT("Max_Day_" & Sheet1!$D$4) doesn't work unfortunately - I'd already
tried that.

Will have a look at the Name Manager but I'm looking for a solution that
uses explicit formulas rather than macros and/or add-ins.

Any idea why the name of the named range no longer shows up in the worksheet
name box when 'Refers to' does employ INDIRECT?

Regards,

Tom

 

[Dave Peterson]

That's the way excel works for those names that are created like this. Same
with the dynamic names.

I still don't understand the reason:
=INDIRECT("Max_Day_" & Sheet1!$D$4)
doesn't work for you.

It worked ok for me.

 

[Tom-S]

Hi Dave. Interesting you got it to work - but frustrating it's not working
for me.

Couple of questions: Are you using Excel 2003, and could you post the exact
formula typed into your 'Refers to' box of the Define Name dialogue?

Regards,

Tom

 

[Dave Peterson]

I selected A1 in Sheet1 and used:
Insert|Name|define:
Max_Day_1
Refers to:
=Sheet1!$A$1

Then I did the same thing for B1 and C1
Max_Day_2
=Sheet1!$B$1
and
Max_Day_3
=Sheet1!$C$1

Then I typed:
=cell("address",a1)
in A1 and dragged to the right so that I could see:
$A$1 $B$1 $C$1

Then I typed 1 in D1
and I used this formula in A5
=INDIRECT("max_day_"&Sheet1!$D$1)
and saw $A$1 as the result.

I changed the value in D1 to 2 and 3 and saw:
$B$1 and $C$1

 

[Tom-S]

But Dave, I was talking about using INDIRECT in the Refers to box as well as
the A5 formula (in your example), in both instances so that cut & paste does
not alter the range which the original ref is pointing to.

If you cut & paste say A1 to A3, then the name Max_Day_1 now refers to A3
instead of A1 (where I wanted it to stay), and the formula in A5 now results
in $A$3 showing.

I'm wanting to protect both the named cell (e.g. A1 here) and the calling
cell (e.g. A5 here) from being altered by the use of cut & paste i.e. (1)
whatever name is defined for A1 stays with A1 and doesn't move, and (2) the
call up formula uses a cell ref as a variable in the name it refers to e.g.
=INDIRECT("max_day_"&Sheet1!$D$1).

Regards,

Tom

 

[Dave Peterson]

I see your problem and I don't have a suggestion using names.