Protect formula against cut & paste

[=?iso-8859-1?Q?Valent=EDn_Play=E1?=]

Hello,

I need to have a worksheet with the formula protected and some cells unprotected to enter the values. The formulas uses the value in the unprotected cell to make calculations.

The problem is that if a user cut an unprotected cell and paste it in another unprotected cell, Excell change the formula.

I 'll try to explain with a simple example. I have all cells of a worksheet protected except A1 and A2. In A3 I have the formula =A1+A2. If I cut cell A1 and paste it in A2 Excel changes the protected formula in A3 to =A2+A2.

Is there any way to avoid this hapening?. I have used all protect option with no results.

Thanks in advance,

Valentín Playá
Madrid, Spain

 

[Dave Peterson]

You could try to train your users not use Cut and paste.

Or maybe you could change those formulas to always point at the cells you want:

=indirect("a1")+indirect("a2")

 

[KL]

Hi Valentin,

See my 2 message in the thread you opened in the Spanish group.

Regards,
KL

Hello,

I need to have a worksheet with the formula protected and some cells unprotected to enter the values. The formulas uses the value in the unprotected cell to make calculations.

The problem is that if a user cut an unprotected cell and paste it in another unprotected cell, Excell change the formula.

I 'll try to explain with a simple example. I have all cells of a worksheet protected except A1 and A2. In A3 I have the formula =A1+A2. If I cut cell A1 and paste it in A2 Excel changes the protected formula in A3 to =A2+A2.

Is there any way to avoid this hapening?. I have used all protect option with no results.

Thanks in advance,

Valentín Playá
Madrid, Spain

 

[Valentín Playá]

Dave,

I cut the users hands when then cut and paste the cells but they find new
ways to broke the formula.

The indirect function works well and is cleaner (less blood)

Thanks,

Valentín
Madrid, Spain
***************************************************

 

[=?iso-8859-1?Q?Valent=EDn_Play=E1?=]

KL,

gracias por tus respuestas en el grupo español. Funciona. Me parece un poco extraño el comportamiento de Excel, a veces se pasa de listo.

Un saludo,

Valentín
******************************************************************************************
"KL" <[email protected]> escribió en el mensaje Hi Valentin,

See my 2 message in the thread you opened in the Spanish group.

Regards,
KL

Hello,

I need to have a worksheet with the formula protected and some cells unprotected to enter the values. The formulas uses the value in the unprotected cell to make calculations.

The problem is that if a user cut an unprotected cell and paste it in another unprotected cell, Excell change the formula.

I 'll try to explain with a simple example. I have all cells of a worksheet protected except A1 and A2. In A3 I have the formula =A1+A2. If I cut cell A1 and paste it in A2 Excel changes the protected formula in A3 to =A2+A2.

Is there any way to avoid this hapening?. I have used all protect option with no results.

Thanks in advance,

Valentín Playá
Madrid, Spain

 

[BlueDaze]

Here's an obtuse way of doing it but should work. Type in cell A3:

=(INDEX($A$1:$A$2,1,1)+INDEX($A$1:$A$2,2,1))

It's not bulletproof, but the only way that the formula will change i
if the entire array is moved, not just a single cell. Depending on you
spreadsheet, maybe you could insert and hide one protected row in th
array, so that the whole array can't be moved