| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Protecting is NOT working right !!!!!!!!!!!!!!
If you have a protected sheet in a workbook, A, and you CUT out some of the
unprotected cells from A and paste them in another workbook - then the "protected" cells in A changes !!!!!!!!! Sample: Insert some dates in column A. Insert formula in column B to extract the dayname from the date in column A. Unprotect column A and protect the sheet. Save the workbook CUT out some cells in column A and insert thenm in new workbook B and save B Look at the formulas in column B in workbook A - they have changed !!!!!!!!!!!!! It's a fatal ERROR I think ! How can this be avoided ? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Protecting is NOT working right !!!!!!!!!!!!!!
You cut the range--that means that each of the formulas that used a cell in that
range that was cut has to point to the new location. It works the same way if you cut and paste into the same worksheet. What would you have excel do? Maybe you could copy|paste and then clear the contents of the original range???? KSO wrote: If you have a protected sheet in a workbook, A, and you CUT out some of the unprotected cells from A and paste them in another workbook - then the "protected" cells in A changes !!!!!!!!! Sample: Insert some dates in column A. Insert formula in column B to extract the dayname from the date in column A. Unprotect column A and protect the sheet. Save the workbook CUT out some cells in column A and insert thenm in new workbook B and save B Look at the formulas in column B in workbook A - they have changed !!!!!!!!!!!!! It's a fatal ERROR I think ! How can this be avoided ? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Protecting is NOT working right !!!!!!!!!!!!!!
You protect a sheet so that a user can't destroy it - right ?
A simple operation like CUT can do the job of destroing a sheet I have protected ! I will have Excel to do what the word "protect" means - protect the sheet ! It's a FATAL ERROR - even a politician can't talk his way of of this ! "Dave Peterson" wrote: You cut the range--that means that each of the formulas that used a cell in that range that was cut has to point to the new location. It works the same way if you cut and paste into the same worksheet. What would you have excel do? Maybe you could copy|paste and then clear the contents of the original range???? KSO wrote: If you have a protected sheet in a workbook, A, and you CUT out some of the unprotected cells from A and paste them in another workbook - then the "protected" cells in A changes !!!!!!!!! Sample: Insert some dates in column A. Insert formula in column B to extract the dayname from the date in column A. Unprotect column A and protect the sheet. Save the workbook CUT out some cells in column A and insert thenm in new workbook B and save B Look at the formulas in column B in workbook A - they have changed !!!!!!!!!!!!! It's a fatal ERROR I think ! How can this be avoided ? -- Dave Peterson . |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Protecting is NOT working right !!!!!!!!!!!!!!
<I will have Excel to do what the word "protect" means - protect the sheet
The way it is defined and explained in HELP is that "protect sheet" means "protect the cells on this sheets that are marked as "protected"" Back to your original question: <How can this be avoided ? Protect all the cells on the sheet -- Kind regards, Niek Otten Microsoft MVP - Excel "KSO" <keld DOT soerensen@os DOT dk wrote in message ... You protect a sheet so that a user can't destroy it - right ? A simple operation like CUT can do the job of destroing a sheet I have protected ! I will have Excel to do what the word "protect" means - protect the sheet ! It's a FATAL ERROR - even a politician can't talk his way of of this ! "Dave Peterson" wrote: You cut the range--that means that each of the formulas that used a cell in that range that was cut has to point to the new location. It works the same way if you cut and paste into the same worksheet. What would you have excel do? Maybe you could copy|paste and then clear the contents of the original range???? KSO wrote: If you have a protected sheet in a workbook, A, and you CUT out some of the unprotected cells from A and paste them in another workbook - then the "protected" cells in A changes !!!!!!!!! Sample: Insert some dates in column A. Insert formula in column B to extract the dayname from the date in column A. Unprotect column A and protect the sheet. Save the workbook CUT out some cells in column A and insert thenm in new workbook B and save B Look at the formulas in column B in workbook A - they have changed !!!!!!!!!!!!! It's a fatal ERROR I think ! How can this be avoided ? -- Dave Peterson . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Protecting is NOT working right !!!!!!!!!!!!!!
I exspect as you write yourself:
"... that "protect sheet" means protect the cells on this sheets that are marked as "protected" that's exactly what NOT is happening in my sample - the cells in column B IS in fact protected but is changed anyway !!!!!! "Niek Otten" wrote: Back to your original question: <How can this be avoided ? Protect all the cells on the sheet ??? ... I'm totally confused now ! How should the user then use the sheet ???????? There IS a FATAL ERROR here - even a politician can't talk his way of of this ! .... what I'm looking for is a work around - do you have one ? -- Kind regards, Niek Otten Microsoft MVP - Excel "KSO" <keld DOT soerensen@os DOT dk wrote in message ... You protect a sheet so that a user can't destroy it - right ? A simple operation like CUT can do the job of destroing a sheet I have protected ! I will have Excel to do what the word "protect" means - protect the sheet ! It's a FATAL ERROR - even a politician can't talk his way of of this ! "Dave Peterson" wrote: You cut the range--that means that each of the formulas that used a cell in that range that was cut has to point to the new location. It works the same way if you cut and paste into the same worksheet. What would you have excel do? Maybe you could copy|paste and then clear the contents of the original range???? KSO wrote: If you have a protected sheet in a workbook, A, and you CUT out some of the unprotected cells from A and paste them in another workbook - then the "protected" cells in A changes !!!!!!!!! Sample: Insert some dates in column A. Insert formula in column B to extract the dayname from the date in column A. Unprotect column A and protect the sheet. Save the workbook CUT out some cells in column A and insert thenm in new workbook B and save B Look at the formulas in column B in workbook A - they have changed !!!!!!!!!!!!! It's a fatal ERROR I think ! How can this be avoided ? -- Dave Peterson . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Protecting is NOT working right !!!!!!!!!!!!!!
You mean your cells in Column B are "locked" and sheet is protected.
The cells are locked as far as anyone being able to edit those cells. BUT...........if you have formulas in those cells, the formulas will update. That's the purpose of locking cells...............no one can edit or overwrite your formulas but allow the formulas to operate as designed. By cutting referenced cells and moving them to another sheet your formulas now refer to the new location just as designed. Excel is doing its job correctly. If you don't want formulas in column B to update, convert them to Values before cutting and moving the referenced cells. Gord Dibben MS Excel MVP On Tue, 16 Feb 2010 07:34:01 -0800, KSO <keld DOT soerensen@os DOT dk wrote: I exspect as you write yourself: "... that "protect sheet" means protect the cells on this sheets that are marked as "protected" that's exactly what NOT is happening in my sample - the cells in column B IS in fact protected but is changed anyway !!!!!! "Niek Otten" wrote: Back to your original question: <How can this be avoided ? Protect all the cells on the sheet ??? ... I'm totally confused now ! How should the user then use the sheet ???????? There IS a FATAL ERROR here - even a politician can't talk his way of of this ! ... what I'm looking for is a work around - do you have one ? -- Kind regards, Niek Otten Microsoft MVP - Excel "KSO" <keld DOT soerensen@os DOT dk wrote in message ... You protect a sheet so that a user can't destroy it - right ? A simple operation like CUT can do the job of destroing a sheet I have protected ! I will have Excel to do what the word "protect" means - protect the sheet ! It's a FATAL ERROR - even a politician can't talk his way of of this ! "Dave Peterson" wrote: You cut the range--that means that each of the formulas that used a cell in that range that was cut has to point to the new location. It works the same way if you cut and paste into the same worksheet. What would you have excel do? Maybe you could copy|paste and then clear the contents of the original range???? KSO wrote: If you have a protected sheet in a workbook, A, and you CUT out some of the unprotected cells from A and paste them in another workbook - then the "protected" cells in A changes !!!!!!!!! Sample: Insert some dates in column A. Insert formula in column B to extract the dayname from the date in column A. Unprotect column A and protect the sheet. Save the workbook CUT out some cells in column A and insert thenm in new workbook B and save B Look at the formulas in column B in workbook A - they have changed !!!!!!!!!!!!! It's a fatal ERROR I think ! How can this be avoided ? -- Dave Peterson . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Protecting is NOT working right !!!!!!!!!!!!!!
USELESS !!
To make it this way - by design - it's useless !!!!!! "Gord Dibben" wrote: You mean your cells in Column B are "locked" and sheet is protected. The cells are locked as far as anyone being able to edit those cells. BUT...........if you have formulas in those cells, the formulas will update. That's the purpose of locking cells...............no one can edit or overwrite your formulas but allow the formulas to operate as designed. By cutting referenced cells and moving them to another sheet your formulas now refer to the new location just as designed. Excel is doing its job correctly. If you don't want formulas in column B to update, convert them to Values before cutting and moving the referenced cells. Gord Dibben MS Excel MVP On Tue, 16 Feb 2010 07:34:01 -0800, KSO <keld DOT soerensen@os DOT dk wrote: I exspect as you write yourself: "... that "protect sheet" means protect the cells on this sheets that are marked as "protected" that's exactly what NOT is happening in my sample - the cells in column B IS in fact protected but is changed anyway !!!!!! "Niek Otten" wrote: Back to your original question: <How can this be avoided ? Protect all the cells on the sheet ??? ... I'm totally confused now ! How should the user then use the sheet ???????? There IS a FATAL ERROR here - even a politician can't talk his way of of this ! ... what I'm looking for is a work around - do you have one ? -- Kind regards, Niek Otten Microsoft MVP - Excel "KSO" <keld DOT soerensen@os DOT dk wrote in message ... You protect a sheet so that a user can't destroy it - right ? A simple operation like CUT can do the job of destroing a sheet I have protected ! I will have Excel to do what the word "protect" means - protect the sheet ! It's a FATAL ERROR - even a politician can't talk his way of of this ! "Dave Peterson" wrote: You cut the range--that means that each of the formulas that used a cell in that range that was cut has to point to the new location. It works the same way if you cut and paste into the same worksheet. What would you have excel do? Maybe you could copy|paste and then clear the contents of the original range???? KSO wrote: If you have a protected sheet in a workbook, A, and you CUT out some of the unprotected cells from A and paste them in another workbook - then the "protected" cells in A changes !!!!!!!!! Sample: Insert some dates in column A. Insert formula in column B to extract the dayname from the date in column A. Unprotect column A and protect the sheet. Save the workbook CUT out some cells in column A and insert thenm in new workbook B and save B Look at the formulas in column B in workbook A - they have changed !!!!!!!!!!!!! It's a fatal ERROR I think ! How can this be avoided ? -- Dave Peterson . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| protecting formulas without protecting sheet so grouping still wor | Excel Discussion (Misc queries) | |||
| Protecting a macro | Excel Discussion (Misc queries) | |||
| Calculate working days but change working week | Excel Discussion (Misc queries) | |||
| protecting... | Excel Discussion (Misc queries) | |||
| up down arrows not working after protecting sheet | Excel Discussion (Misc queries) | |||
Linear Mode
