Выпадающие списки с удалением⁠⁠

В комментариях @Klopuz попросил показать способ как создать выпадающий список с удаляющимися значениями и вот оно решение.

Этот приём удобно применять при распределении дежурств (товаров), чтобы не запутаться и не назначить одного человека дважды, а также наглядно видеть тех, кто остался в запасе. В итоге у нас получится вот такая таблица:

1. Сперва необходимо подсчитать, кто из имеющихся сотрудников уже назначен на дежурство и на сколько смен. Для этого добавим к серой таблице ещё один столбец, в который введём формулу: =СЧЁТЕСЛИ($B$2:$B$8;E2)

=COUNTIF($B$2:$B$8;E2)

Эта формула вычисляет, сколько раз имя сотрудника встречалось в диапазоне с именами.

2. Далее выявим свободных сотрудников. Добавим ещё один столбец и введём в него формулу, которая будет выводить номера свободных сотрудников:

=ЕСЛИ(F2-G2<=0;"";СТРОКА(E2)-СТРОКА($E$2)+1)

=IF(F2-G2<=0;"";ROW(E2)-ROW($E$2)+1)

3. Теперь создадим непрерывный (без пустых ячеек) список свободных сотрудников для связи, на следующем этапе, с выпадающим списком. Для этого добавим ещё один столбец и введём в него формулу:

=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);"";ИНДЕКС($E$2:$E$10;

НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))

=IF(D2>COUNT($H$2:$H$10);"";INDEX($E$2:$E$10;

SMALL($H$2:$H$10;ROW(E2)-1)))

Эта формула выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.

4. Далее создаём именованный диапазон сотрудников нажав Ctrl+F3 или на вкладке «Формулы» - «Диспетчер имён», где из столбца списка E2 зададим название «Имена», а в строке «Диапазон» введём формулу:

=СМЕЩ(Лист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))

5. Создадим выпадающий список выделив ячейки B2:B8 и на вкладке «Данные» - «Проверка данных» - «Список» - «Источник» =Имена

Готово) Теперь при выборе сотрудников их имена будут автоматически удаляться из выпадающего списка, оставляя только тех, кто ещё свободен.