Protecting is NOT working right !!!!!!!!!!!!!!

[KSO]

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]

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]

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 !

 

[Niek Otten]

<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

 

[KSO]

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 !!!!!!

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 ?

 

[Gord Dibben]

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

 

[KSO]

USELESS !!

To make it this way - by design - it's useless !!!!!!