Imagine the following Excel table:

`B6`

is `=ZÄHLENWENN(A3:C4;"<>")`

(`COUNTIF`

, I think, in English), so it counts the amount of pupils sitting in the room. The teacher can insert their names in `A3:C4`

and the formula counts.
I also put in a formatting that paints the seats that are not occupied gray.

I want the teacher to edit the names, but not the formula, so I locked the
worksheet after unlocking `A3:C4`

.

But what if Olive wants to sit next to Verena? When the teacher just moves her cell, he breaks the formatting, even though he shouldn't be able to! Sometimes, formulas dependent on the cells that are moved get messed up afterwards as well.

So, how can I prevent the user from destroying formatting and formulas?

**Edit** for @Rajesh S:

Imagine, the teacher's desk is standing in the middle in front of the class and we want to see which pupil is sitting next to it. The formula, put in `B7`

, would be `=WENN(B3="";"—";B3)`

(if `B3`

is `""`

, return `"—"`

, else return `B3`

). When Olive is sitting in `B3`

, the formula returns `Olive`

. If the teacher moves Olive to `C4`

, **the formula still returns Olive**.

**Edit** for @RajeshS and **clarity**:

I am not talking about editing the values of the cells. When I say *move*, I mean *click at Olive, drag it to the empty seat and drop it there*.

**Edit** for @RajeshS:

This is what I did to get your proposed solution:

- New Excel work sheet
- Go to cell A66
- Colspan A66:C66
- Enter text
`Desk position (row)`

- Go to cell A68
- Conditional formatting
`=ISTLEER(A68)`

on`=$A$68:$C$69`

(fill color) - Go to cell A71
- Enter text
`Total students`

- Go to cell B71
- Enter text
`=ZÄHLENWENN(A68:C69;"<>"&"")`

- Go to cell A73
- Colspan A73:C73
- Enter text
`Students position`

- Enter texts
`Name`

,`Original`

and`Current`

into A74, B74 and C74 - Enter texts
`Adam`

,`Olive`

,`Claus`

,`Bertram`

and`Verena`

into A75, A76, A77, A78, A79 - Enter texts
`1`

,`2`

,`3`

,`4`

,`5`

and`6`

into B75, B76, B77, B78, B79, B80 - Go to cell C75
- Enter formula
`=KKLEINSTE(WENN($A75=$A$68:$C$69; ZEILE($A$68:$C$69)-ZEILE($A$68)+1); ZEILE($1:$1))`

and create the`{}`

with Shift+Enter - Enlarge the formula till C80
- Enter students' names into the colored area
- Edit the format of A66:C66 (uncheck the lock flag)
- Apply sheet lock (uncheck every permission but the selection of unlocked cells)
- This is how the teacher finds the table: Exactly like in your first screenshot
- He clicks at Olive and drags her into C69
- The column from C75 now has the values 1, 2, 1, 2, 2, 1 like in your second screenshot, but B68 is
*not*filled with the color defined in the conditional formatting. If he had*deleted*her from her original seat and*re-typed*her into the new one, it would have worked. And exactly*this is the problem*: A user can modify parts of a worksheet is shouldn't be able to modify, by moving cells.