Seating plans using a dynamic list

[cjms85]

Hi all,
I'm a teacher and I'm trying to put together a spreadsheet that I can quickly use to generate class seating plans.

On the first sheet (Student_List) I have a master list of the pupil forenames, surnames and some other class details. I've also created a dynamic list with drop down boxes, so that when a pupils name is selected from the drop-down list it can't be selected again. from the next drop-down list below.

What I'd like to do is have the dynamic drop-down list organised in an 8 col. x 4 rows grid, see the second sheet (Eg1_searint), representing my desk layout. That way, I can allocate a student a desk in their relative position in the class and then select another student for a different desk. Having the dynamic list is really important, as I want to be able to prevent myself from allocating the same student two desks.

Here's a link to my sample workbook:

Seating plan example.xlsx

drive.google.com

I've used the method given on this website to create the drop down list:

How to hide previously used items in dropdown list?

This page explains how to hide previously used items in a dropdown list, ensures that each item is only selected once, maintains a clean and organized dropdown

www.extendoffice.com

Any advice would be greatly appreciated.

 

[Yard]

Can you just use some conditional formatting to indicate the a duplicate?

 

[Fluff]

If you apply the data validation to your seating grid (ie B3:I6) then change the formula in M4 on the student list to
=IF(COUNTIF(Eg1_seating!$B$3:$I$6,K4)>=1,"",ROW())
and fill down.

 

[Snakehips]

Forgive me if I am not understanding correctly.
I see how selecting a name in O removes that name from subsequent dropdown lists but it does not indicate, in any way, that pupils allocated desk position?
Maybe you do not need such an indication in Student_List?
So, are you entering names of allocated students to Eg1_Seating and then going back to Student_List to confirm said student has been allocated?

Maybe, In addition to changing the formula in column M as @Fluff suggests, above, you have the Data Validation/Dynamic List as a dropdown list directly in the cells of Eg1_Seating! B3:I6 ?
In which case I would imagine that column O would be redundant.