Imagine the following Excel table:

Seating in classroom

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.

Example filling

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.

Broken formatting after moving cell

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:

  1. New Excel work sheet
  2. Go to cell A66
  3. Colspan A66:C66
  4. Enter text Desk position (row)
  5. Go to cell A68
  6. Conditional formatting =ISTLEER(A68) on =$A$68:$C$69 (fill color)
  7. Go to cell A71
  8. Enter text Total students
  9. Go to cell B71
  10. Enter text =ZÄHLENWENN(A68:C69;"<>"&"")
  11. Go to cell A73
  12. Colspan A73:C73
  13. Enter text Students position
  14. Enter texts Name, Original and Current into A74, B74 and C74
  15. Enter texts Adam, Olive, Claus, Bertram and Verena into A75, A76, A77, A78, A79
  16. Enter texts 1, 2, 3, 4, 5 and 6 into B75, B76, B77, B78, B79, B80
  17. Go to cell C75
  18. 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
  19. Enlarge the formula till C80
  20. Enter students' names into the colored area
  21. Edit the format of A66:C66 (uncheck the lock flag)
  22. Apply sheet lock (uncheck every permission but the selection of unlocked cells)
  23. This is how the teacher finds the table: Exactly like in your first screenshot
  24. He clicks at Olive and drags her into C69
  25. 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.
  • I think you don't have any issue,, since your desks are in Data Range A3:C4 which is unlocked as you have mentioned in OP and Formula cell is out of that Range so that naturally it's LOCKED !! – Rajesh S Jan 9 '18 at 10:10
  • But the user cannot adjust the automatic formatting – it is greyed out because of the lock. When he moves Olive to Verena, he messes up the formatting nonetheless. – Bowi Jan 9 '18 at 10:48
  • No,, once the Format is applied to the Cell or Range of Cell will exists until either Format is being removed or modified. Replacing or even deleting the Cell value can't spoils it. – Rajesh S Jan 9 '18 at 10:53
  • @RajeshS, I've edited my example to show a formula lieing outside the editable range that nontheless gets corrupted when the user moves a cell inside the editable range. – Bowi Jan 9 '18 at 10:57
  • your 25 points final editing is 100% correct. Now let me say one thing, the Data range A68:C69 is EDITABLE and it has to be EDITABLE ZONE otherwise how could the Teacher reshuffle the students sitting arrangements, write ! Also, to use my SOLUTION you need to DELETE the NAME from the CELL first, then RE-WRITE it in OTHER CELL. There is NO DRAG & DROP method is been used, ok ! Drag & Drop is only possible with the SHAPE or OBJECTS and to control the entire procedure VBA code is required, my SOLUTION is FORMULA BASED. – Rajesh S Jan 12 '18 at 11:31

Now I'm suggesting you comparatively a better solution. Check the Screen Shots below.

Original Seating:

enter image description here

Modified Seating Olive Next to Verena:

enter image description here

Now how to accomplish all this.

  1. Write this formula to Count Student, =COUNTIF(A68:C69,"<>"&"")
  2. Select the Original Seating (Data Range, A68:C69).
  3. Apply Conditional formatting, use this Formula, =ISBLANK(A68).
  4. Draw the Data as I've done and write this Formula in C75(Red Cell), drag till last.

    {=SMALL(IF($A75=$A$68:$C$69, ROW($A$68:$C$69)-ROW($A$68)+1), ROW($1:$1))}


It's CSE Formula so finish with Ctrl+Shift+Enter.

You have 6 Seats(Cells), where last Seat(Cell) in Blank. Just Check STUDENT POSITION, after Verena the Cell is left BLANK it's Seat is 6 & Row is 2, which changes as you shift Olive next to Verena, also the Olive's Row position.

For all 3 Formulas change the Data Range as your requirements.

Hope this help you.

  • 1
    Thank you very much for all that effort, but in fact, this does not help me at all. My problem is that I have an Excel sheet with lots of formulas and conditional formattings, and that even if the sheet is locked, the user breaks the formatting inside and formulas outside the editable area when moving (drag/drop) cells inside the editable area. I do not understand which parts of your example I could use to solve the problem, for me it is just "doing different things with different data". – Bowi Jan 9 '18 at 14:27
  • My solution pertains to the main issue, if Olive will shift to other place, CHECK both EDIT!! And protection is simple one, Select the Data Range you want to use for the exercise and go to Cell Format, Click Protection TAB, then uncheck the Lock button. Next is find Review TAB & Protect the Sheet,,, Done. – Rajesh S Jan 10 '18 at 7:54
  • Currently trying. Where does your table start, is it A66? (The downvote wasn't me!!) – Bowi Jan 10 '18 at 8:15
  • I seem to use a different kind of excel than you do – I did it like you, and wenn I move (drag/drop) Olive, her old seat is not highlighted as empty. The formula did not break, but in my real sheet I have to reference a single cell, so I cannot use an array formula for that like you do. – Bowi Jan 10 '18 at 8:23
  • The Data Range starts from A68. And I'm using Excel 2016 only. Still you are NOT READING the Answer carefully. Read what I've suggested HOW TO ACCOMPLISH ALL THIS,,!! – Rajesh S Jan 10 '18 at 8:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.