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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
corrupted formula in protected worksheet
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. |
#2
|
|||
|
|||
corrupted formula in protected worksheet
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. 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 |
#3
|
|||
|
|||
corrupted formula in protected worksheet
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. |
#4
|
|||
|
|||
corrupted formula in protected worksheet
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. |
#5
|
|||
|
|||
corrupted formula in protected worksheet
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 |
#6
|
|||
|
|||
corrupted formula in protected worksheet
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. "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. |
#7
|
|||
|
|||
corrupted formula in protected worksheet
Not what you want to hear but the answer is no.
-- HTH... Jim Thomlinson "Tom-S" wrote: 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. "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. |
#8
|
|||
|
|||
corrupted formula in protected worksheet
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 . |
#9
|
|||
|
|||
corrupted formula in protected worksheet
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 |
#10
|
|||
|
|||
corrupted formula in protected worksheet
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 . |
Thread Tools | |
Display Modes | |
|
|