Создайте умные таблицы для своих справочников. Присвойте умным таблицам имена, совпадающие с названием столбца справочника.
Лист Просто в файле с примером. Для генерации списков используйте формулу такого вида (на примере справочника Автомобиль): =ИНДЕКС(Автомобиль;СЛУЧМЕЖДУ(1;ЧСТРОК(Автомобиль))) или на басурманском
=INDEX(Автомобиль;RANDBETWEEN(1;ROWS(Автомобиль)))
Лист Сложнее в файле с примером. Чтобы иметь возможность динамически выбирать в первой строке справочник для формирования списков надо создать именованный диапазон Lists при помощи формулы: =СМЕЩ(Справочники!$A$1;0;0;1;СЧЁТЗ(Справочники!$1:$1)) или
=OFFSET(Справочники!$A$1;0;0;1;COUNTA(Справочники!$1:$1))
Указанный именованный диапазон используйте в качестве источника выпадающего списка функции Проверка данных (смотри ячейки A1:C1):
В этом случае для формирования значений списка будем использовать такую формулу: =ИНДЕКС(ДВССЫЛ(A$1);СЛУЧМЕЖДУ(1;ЧСТРОК(ДВССЫЛ(A$1))))
=INDEX(INDIRECT(A$1);RANDBETWEEN(1;ROWS(INDIRECT(A$1))))