how to lock excel from dragging?

[babaso_tawase]

how to lock excel from dragging in unlocked cell in locked excel sheet?
[TABLE="class: outer_border, width: 200, align: center"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
In Column A is unlocked and in B locked Cell, if dragged unlocked cells then formula in Column B changes (as formula depends on cell value in column A) to no of cells that dragged.
I want lock dragging function in excel , is it possible?
Thanks.

 

[Gerald Higgins]

Have you turned protection on ?

Locking / unlocking cells has no effect until worksheet protection is turned on.

 

[babaso_tawase]

Yes, sheet is protected using password.
In locked cell , protection is on and in unlocked cell protection is off.
Thanks.

 

[Gerald Higgins]

No, that's not how it works, protection is either off for the whole sheet, or on for the whole sheet.

You can't have protection ON for one cell or group of cells, and OFF for another cell or group of cells on the same sheet.

Individual cells can be locked or unlocked.
But this makes no difference if the worksheet is not protected - you will be able to make changes to any cell.

But if you turn protection on, you will then only be able to make changes to cells that are UNlocked.

 

[babaso_tawase]

No, that's not how it works, protection is either off for the whole sheet, or on for the whole sheet.

You can't have protection ON for one cell or group of cells, and OFF for another cell or group of cells on the same sheet.

Individual cells can be locked or unlocked.
But this makes no difference if the worksheet is not protected - you will be able to make changes to any cell.

But if you turn protection on, you will then only be able to make changes to cells that are UNlocked.

Actually unlocked cells for enter raw data to user and locked cells contains formulas to process data.

So we can't lock whole sheet.
and it is possible to lock or unlock cell or group of cells by using format cells option, in that protection tab by selecting lock or unlock option.then protecting sheet using password.

 

[Gerald Higgins]

I'm having difficulty understanding what you are saying, but I'll try to explain again.

Individual cells (and groups of cells) can be locked, or unlocked.
The default setting on a new worksheet is normally locked.
You change the setting in the Format Cells dialog box, on the Protection tab.

On the Protection tab in the Format Cells dialog box, it tells you that

Locking cells . . . has no effect until you protect the worksheet . . .

So if you want some cells to be locked and others to be unlocked, and for the locked status to actually take effect . . .

1) Make sure that the cells you want to be locked, are actually locked, on the Format Cells dialog box
2) Make sure that the cellls you want to be unlocked, are actually unlocked, on the Format Cells dialog box
3) Turn worksheet protection ON (and you can set a password for this if you like, but it's not compulsory).

By the way, in my opinion, there's usually no need to quote entire posts on this board, it just makes the whole thread longer than it needs to be.

 

[babaso_tawase]

Yes Sir, all done as mentioned above message. I actually want lock dragging function on unlocked cells.
Thanks

 

[Jaafar Tribak]

Yes Sir, all done as mentioned above message. I actually want lock dragging function on unlocked cells.
Thanks

In Excel 2010 - File>Options>Advanced Options>untick Drag cells checkbox.

 

[babaso_tawase]

Is it possible for excel 2003?

 

[Jaafar Tribak]

Is it possible for excel 2003?

Yes.
TOOLS, OPTIONS, EDIT, checkbox ALLOW DRAG AND DROP

Note that this is an application level setting so if the workbook is opened by a user on a different machine you may need to set this with VBA upon opening/Activating the workbook as follows : Application.CellDragAndDrop = False