A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

corrupted formula in protected worksheet



 
 
Thread Tools Display Modes
  #21  
Old May 29th, 2010, 03:38 PM posted to microsoft.public.excel.misc
Tom-S[_2_]
external usenet poster
 
Posts: 68
Default corrupted formula in protected worksheet

Dave, for information, I've come up with a workaround for this problem.

The names that originally referred to say cells A1:C1, now refer to cells
A3:C3. (The 'Refers to' box contains the default type ref, and doesn't use
INDIRECT.)

A3:C3 contain formulas =INDIRECT("A1"), =INDIRECT("B1"), =INDIRECT("C1")

Cells A3:C3 are also locked and formatted ;;; so content only shows in
formula bar. When the sheet is protected, cells A3:C3 cannot be cut & pasted.

Meantime, cells A1:C1 accept the user input values of the names Max_Day_1 to
3.

Call-ups to the names can retain the original format =INDIRECT("Max_Day_" &
Sheet1!$D$4), where cell D4 on Sheet1 contains a user input of either 1, 2 or
3.

All of this means that if the user decides to cut & paste any of A1:C1, the
names stay referenced to A3:C3, and the call-ups still work, which is what I
was trying to achieve.

Thanks for considering the problem along the way.

Regards,

Tom


"Dave Peterson" wrote:

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

Tom-S wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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

"Tom-S" wrote:

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


"Dave Peterson" wrote:

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 wrote:

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.

  #22  
Old May 29th, 2010, 04:29 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 19,791
Default corrupted formula in protected worksheet

Glad you found a solution that works for you.

Tom-S wrote:

Dave, for information, I've come up with a workaround for this problem.

The names that originally referred to say cells A1:C1, now refer to cells
A3:C3. (The 'Refers to' box contains the default type ref, and doesn't use
INDIRECT.)

A3:C3 contain formulas =INDIRECT("A1"), =INDIRECT("B1"), =INDIRECT("C1")

Cells A3:C3 are also locked and formatted ;;; so content only shows in
formula bar. When the sheet is protected, cells A3:C3 cannot be cut & pasted.

Meantime, cells A1:C1 accept the user input values of the names Max_Day_1 to
3.

Call-ups to the names can retain the original format =INDIRECT("Max_Day_" &
Sheet1!$D$4), where cell D4 on Sheet1 contains a user input of either 1, 2 or
3.

All of this means that if the user decides to cut & paste any of A1:C1, the
names stay referenced to A3:C3, and the call-ups still work, which is what I
was trying to achieve.

Thanks for considering the problem along the way.

Regards,

Tom

"Dave Peterson" wrote:

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

Tom-S wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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" wrote:

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 wrote:

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

"Tom-S" wrote:

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


"Dave Peterson" wrote:

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 wrote:

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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.