Too often this academic year my colleague has had to redraft the tutor plan. In my role as lead for CPD, too often I have created Professional Development lists, scrutinising the lists to make sure I have not forgotten someone, somewhere. And now, I present to you, the “ever reducing drop-down list, as you assign staff to a category” spreadsheet.
As staff are used, they are automatically removed from the drop down list. A simple display cell formula (G3=D2) then show the name in the correct place on the map / layout / seating plan.
Download the spreadsheet and added your staff names
Download the spreadsheet. Add your staff names to the “Lists” worksheet in column B.
The worksheet checks if the name in that row has been assigned in column D in Tutor Plan.
=IF(COUNTIF(‘Tutor Plan’!$D$2:$D$135,B1),””,ROW())
Unassigned Staff
In column D, an array formula has been used to identify the unassigned staff. This list is named and used for the data validation drop down list.
In this example, cells D1:D135 are selected, and this formula is array entered (highlight the cells and press Ctrl+Shift+Enter). When the array formula has been applied you see it is enclosed by { }.
This is a multi-cell array formula (by DanielM) moves blank cells to the bottom of the list.
Define a Named Range
A dynamic “named range” is used for the unassigned guests lists, this is then used for the drop-down list.
- On the Excel Ribbon, click the Formulas tab
- Click Define Name
- In the New Name dialog box, type NameCheck as the name
- In the Refers to box, type this OFFSET formula, then click OK
=OFFSET(Lists!$D$1,0,0,COUNTA(Lists!$D$1:$D$135) -COUNTBLANK(Lists!$D$1:$D$135),1)
Add the Data Validation Drop Down Lists
Next, you’ll add the drop down lists:
- Click Data Validation
- In the Data Validation dialog box, from the Allow drop down, select List
- In the Source box, type: =NameCheck
- Click OK
The drop down lists are added to the cells, and you can select a guest name.
Assign the Guest Seats
Now you can select staff names from the drop down lists on the Tutor Plan.
You will need to add categories for the staff that are not available for tutoring duties or you could simply not add their names to the list in the first place.
We preferred to add all staff and add categories, so that all names were assigned. This means the spreadsheet could be reused for all assigning tasks, INSET, parents evening and so on, without having to modify the lists.
[qr_code_display]