Contents
I know what to do but I don’t know
where to put the bodies…
Formulation of the problem
As an example, we have a weekly duty schedule, which must be filled in with the names of employees, and for each employee the maximum number of working days (shifts) is limited. The ideal option would be to organize a drop-down list in cells B2:B8, but at the same time make it so that already busy employees are automatically removed from the drop-down list, leaving only free ones:
To implement such a variant of the drop-down list, we will perform a few simple steps.
Step 1. Who works how much?
First, let’s calculate which of our employees is already assigned to duty and for how many shifts. To do this, add another column to the green table, enter the following formula into it:
=COUNTIF($B$2:$B$8;E2) or in English =COUNTIF($B$2:$B$8;E2)
In fact, the formula simply calculates how many times the employee’s name occurs in the range of names.
Step 2. Who else is free?
Now let’s find out which of our employees is still free, i.e. has not exhausted the supply of allowable shifts. Let’s add another column and enter a formula in it that will display the numbers of free employees:
=ЕСЛИ(F2-G2<=0;"";СТРОКА(E2)-СТРОКА($E$2)+1) или в англоязычной версии =IF(F2-G2<=0;"";ROW(E2)-ROW($E$2)+1)
Step 3. We form the list
Now we need to form a continuous (without empty cells) list of free employees for communication – in the next step – with a drop-down list. To do this, add another column and enter into it such a scary at first glance formula:
=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);»»;ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))
or, respectively,
=IF(D2>COUNT($H$2:$H$10);»»;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1)))
With all the outward creepiness of the look, this formula does one simple thing – it displays the next employee name (using the SMALL function) from the list or an empty cell if the names of free employees have already run out.
Step 4. Create a named range of free employees
- in Excel 2003 and older, go to the menu Insert – Name – Define
- in Excel 2007 and newer – click the button Name Manager tab Formulas
and create a new named range Names according to the following formula:
=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))
в англоязычной версии =OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-COUNTBLANK(Лист1!I$2:I$10))
In fact, we simply give the range of occupied cells in the blue column its own name. Names.
Step 5. Create a Dropdown List in Cells
It remains to select cells B2:B8 of our chart and add a drop-down list with range elements to them Names. For this
- in Excel 2003 and older – open the menu Data – Check (Data – Validation),
- in Excel 2007 and newer – click the button Data Validation tab Data
In the window that opens, select the option from the list of valid values List and I will point out Source data:
That’s all! Now, when employees are assigned to duty, their names will be automatically removed from the drop-down list, leaving only those who are still free.
- 4 Ways to Create a Dropdown List in Excel Sheet Cells
- Creating Dependent Dropdowns
- Auto Generate Dropdown Lists with PLEX Add-In Tools
- Selecting a photo from the dropdown list