Очень часто при использовании связки Выпадающий список-Именованный диапазон возникает проблема: при добавлении новых данных в диапазон они не отразятся сразу в списке - для этого необходимо будет изменить адрес именованного диапазона. Либо указать заранее расширенный диапазон. Но тогда появляются лишние пустые значения в списке, что тоже не очень-то красиво и совсем не удобно, особенно если список не маленький. И вот здесь помогут так называемые Динамические диапазоны. Создав вместо обычного именованного диапазона динамический, уже не придется каждый раз менять адрес диапазона для отображения в списке всех добавленных значений. И отображаться будут только значения, никаких пустых строк.
Вызываем Диспетчер создания имен(Ctrl+F3), задаем имя диапазона и в поле Диапазон
- Где
Лист2!$A$1 - первая ячейка значений для выпадающего списка - а
Лист2!$A$1:$A$1000 максимальный диапазон ячеек, в котором будут храниться значения для списка. Если значений может быть более 1000, то необходимо увеличить диапазон $A$1:$A$1000 на необходимое количество строк. Хотя мне лично страшно представить себе такой выпадающий список. Но динамический диапазон может ведь пригодиться и для других целей
В чем главный недостаток этого метода: если добавить в список значений пропуск между значениями в виде пустой строки - список отобразит не все значения списка, т.к. СЧЕТЗ считает количество непустых ячеек:
Изменяемые ссылки для этой формулы такие же, как и в случае с вариантом через СМЕЩ:
Лист2!$A$1 - первая ячейка значений для выпадающего спискаЛист2!$A$1:$A$1000 максимальный диапазон ячеек, в котором будут храниться значения для списка
Однако метод через данную формулу лишен недостатка предыдущего метода: если добавить в список разрыв в виде пустой строки - список отобразится правильно, с этим разрывом и всеми значениями:
Примечание: созданные таким образом диапазоны нельзя использовать в составе функции ДВССЫЛ
(INDIRECT) для создания зависимых выпадающих списков - список просто не будет работать
Tips_Lists_Dinamic_Range.xls (37,5 КиБ, 6 823 скачиваний)
Есть вариант ещё интереснее.http://www.excel-vba.ru/chto-umeet-excel/vypadayushhie-spiski/
Размещаем список в "Таблице" (Вставка\Таблица)
Создаём Диапазон ссылающийся на часть таблицы с данными например =Таблица1[Почта](если заголовок списка "Почта" в таблице "Таблица1")
Далее всё как указано в основном описании по созданию списка в третьем варианте отсюда:
При необходимости увеличения или изменения состава списка просто добавляете значения вниз таблицы со списком (таблица сама расширится включив новое значение в список) и сортируете. Пустые значения упадут вниз и их можно просто исключить из таблицы или оставить.
При изменении заголовка списка ссылка в именованном диапазоне поменяется сама.
Важный момент в настройках должна быть включена функция "Формулы\Использовать имена таблиц в формулах".