Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
protect formulas in locked cells from changing if data is moved
I have locked and protected a worksheet leaving only "Select unlocked cells"
checked. The problem is that if a user "moves" an entry in an unlocked cell, either by ctrl X or drag and drop the cell references in the locked formulae that referred to the original position change to the new position. Is there any way of preventing this without turning off the drag and drop functionality? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
protect formulas in locked cells from changing if data is moved
Assuming you aren't working with closed workbooks, you could always use the
INDIRECT function in your formulas, i.e: instead of =A1+B1 it would be =INDIRECT("A1")+INDIRECT("B1") -- John C "Cleve" wrote: I have locked and protected a worksheet leaving only "Select unlocked cells" checked. The problem is that if a user "moves" an entry in an unlocked cell, either by ctrl X or drag and drop the cell references in the locked formulae that referred to the original position change to the new position. Is there any way of preventing this without turning off the drag and drop functionality? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
protect formulas in locked cells from changing if data is move
Thanks, but it is a closed workbook.
"John C" wrote: Assuming you aren't working with closed workbooks, you could always use the INDIRECT function in your formulas, i.e: instead of =A1+B1 it would be =INDIRECT("A1")+INDIRECT("B1") -- John C "Cleve" wrote: I have locked and protected a worksheet leaving only "Select unlocked cells" checked. The problem is that if a user "moves" an entry in an unlocked cell, either by ctrl X or drag and drop the cell references in the locked formulae that referred to the original position change to the new position. Is there any way of preventing this without turning off the drag and drop functionality? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
protect formulas in locked cells from changing if data is move
Actually I'm not sure what has happenned. I can get the Indirect function to
work provided I keep it simple, no if functions. It also returns a 0 if I try and use the result in a subsequent formula. There is also a bigger problem with using it, it will take ages to edit the workbook and will increase the size by a huge amount. Thanks Cleve "John C" wrote: Assuming you aren't working with closed workbooks, you could always use the INDIRECT function in your formulas, i.e: instead of =A1+B1 it would be =INDIRECT("A1")+INDIRECT("B1") -- John C "Cleve" wrote: I have locked and protected a worksheet leaving only "Select unlocked cells" checked. The problem is that if a user "moves" an entry in an unlocked cell, either by ctrl X or drag and drop the cell references in the locked formulae that referred to the original position change to the new position. Is there any way of preventing this without turning off the drag and drop functionality? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Viewing Formulas in Locked Cells | Excel Discussion (Misc queries) | |||
Locked cells with Protect Sheet. | Excel Discussion (Misc queries) | |||
Protect shouldn't default to "allow users to select locked cells" | Setting up and Configuration of Excel | |||
sort data without formulas in other cells changing? | Excel Discussion (Misc queries) | |||
Can I protect a spreadsheet from being deleted or moved? | Excel Discussion (Misc queries) |