Раскрывающийся список проверки данных не будет сотрудничать с вами в этом. Он просто хочет посмотреть на статический список или на те ячейки, о которых вы говорите, не думая о том, что в них.
Поскольку мы не можем заставить его делать то, что мы хотим, давайте пойдем на компромисс - мы дадим ему непрерывный диапазон, который показывает только те значения, которые мы хотим видеть (плюс пробелы в конце, куда люди в основном не смотрят).
В моем примере ячейками, которые содержат значения (и пробелы) для моего списка проверки данных, являются A1:A15. Я собираюсь перейти к другому столбцу и ввести следующую формулу:
=IFERROR(INDEX($A$1:$A$15,SMALL(IF($A$1:$A$15<>"",ROW($A$1:$A$15),999),ROW(1:1))),"")
Мне нужно ввести это как формулу массива, поэтому я нажму CTRL + SHIFT + ENTER, когда закончу вводить формулу. Далее я скопирую / вставлю или заполню 15 строк. Результатом будут все мои непустые значения из A1:A15, а затем все мои пробелы в конце.
Теперь я открою диалоговое окно «Проверка данных» и укажу свой список на ячейки, содержащие мои формулы (не A1:A15). Пробелы все еще там, но в конце, так что никому не нужно их видеть, если они не прокрутят там внизу, чтобы ничего не посмотреть.
Он не совсем чистый, но он динамический и избегает макросов.
Так что же сделала эта формула?
=INDEX($A$1:$A$15,
смотрит на исходный диапазон, который имеет ваши значения и пробелы, и позволяет вам сказать, на какую ячейку вы хотите посмотреть, по номеру.
SMALL(
смотрит на набор чисел, отсчитывает от самых маленьких и возвращает обратно,
IF($A$1:$A$15<>"",
проверяет, соответствует ли каждая ячейка в вашем диапазоне чему-либо, кроме пустой строки
ROW($A$1:$A$15)
возвращает номер строки, если это так,
,999),
и возвращает 999, если это не так. Если у вас более 1000 строк, вам понадобится большее число, но этого (надеюсь) более чем достаточно для ваших вариантов проверки данных.
Функция SMALL теперь имеет список номеров строк с 999 вместо фактического номера строки для любых пробелов.
ROW(1:1)
динамический способ начать отсчет с любой ячейки, в которой вы вводите эту формулу. Когда вы копируете формулу вниз, число растет. Это сообщает маленькому, что вам нужно 1-е наименьшее число в 1-й строке, содержащей формулу, 2-е наименьшее в строке 2 и т.д.
SMALL возвращает номер строки обратно в INDEX , который дает вам значение для этой строки. Все эти 999-е в конце превращаются в ошибки #REF, но мы спокойно возвращаем их к пустым местам с помощью IFERROR .
Эту формулу сложно понять. Если у вас возникли проблемы, попробуйте настроить образцы данных в A1:A15 на новом листе, выбрать ячейку в другом столбце и скопировать мою формулу точно в строку формул (не забудьте CTRL+SHIFT+ENTER). Поиграйте с этим немного, и вы должны научиться этому.
Удачи!
