Defined name gets moved with cut and paste

[TomM]

Using Excel 2007 under WinXP, I am trying to prevent defined names from getting moved when the user performs a cut and paste from one cell to another.

My worksheet is a data entry form wherein all cells are locked except for the cells where you enter data. Each one of these data cells has a defined name associated with it.

If a user accidentally enters data into the wrong cell, and then executes a cut/paste to correct things, the worksheet is corrupted.

One possibility is to turn off the ability to "cut". Any suggestions for a cleaner solution?

Thanks,
Tom

 

[Peter_SSs]

Welcome to the MrExcel board!

See if defining your named range(s) in the 'Refers to:' box like this is any help.

=INDIRECT("Sheet1!A1")

 

[TomM]

If I understand the INDIRECT function, it will not help me. I am using Excel as, basically, a data entry form.

If I put a function (such as INDIRECT) in a cell, that would prevent the defined name from being wiped out, but the name's association with the correct cell (via INDIRECT) would be wiped out.

Please correct me if I am missing something.

Thanks,
Tom

 

[Peter_SSs]

If I put a function (such as INDIRECT) in a cell, ..

I am not suggesting putting INDIRECT in a cell, I am suggesting using it to define your name. Try these steps in a new workbook.

1. In cell A1 put a 2

2. Formulas ribbon tab|Define name|Name: Test |Refers to: =INDIRECT("A1")|OK

3. In cell D1 put the formula
=Test
At this point D1 should show 2, since the named range Test is pointing to cell A1

3. Cut cell A1 and Paste it somewhere else.
At this point D1 should show 0 (because the named range 'Test' is still pointing to cell A1 which is now blank)

4. Now put a 3 in cell A1
Cell D1 should now show 3, again showing that the named range was not moved by the Cut/Paste

 

[TomM]

Yes, Peter, that *does* work.

I don't yet understand INDIRECT when it uses quoted cell refs {INDIRECT("A1")}, but that simply means I need to spend some more time with it.

Thank you for your spot-on help.