Здравствуйте! Пока все тапают на хомяка и решают автоматизацию кликов, я решил создать таблицу по выдаче самого выгодного вложения в предприятия.
Прикладываю таблицу, которую смог сделать на данный момент, но я так и не нашёл способ автоматизации протягивания формул.Идея такая: в умной таблице можно выбрать уровень из тех, что имеется у конкретного предприятия в базе Данных, таблица автоматически подтянет данные по Price, Profit и PPM (PPM высчитывается по формуле Profit / Price), когда все уровни выбраны, отфильтровать PPM по убыванию, прокачать это предприятие в Хомяке, ввести новые данные в базу данных, в таблице изменить уровень на новый (в идеале, если бы уровень менялся на новый автоматически, но с возможностью выбора ), и показать самое выгодное предприятие уже на основе новых данных, и так бесконечно.

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

Выбор уровня я сделал так: сформировал умную таблицу, в ячейке lvl - данные, проверка данных, тип данных "список", указываю вручную диапазон =Данные!$N$2:$N$16, жму ОК. На данный момент, весь диапазон пустой, кроме первой строчки. Это нужно для того, чтобы при добавлении новых строк уровней с данными в выпадающем списке автоматически отображались новые уровни. 2 примечания - список всё равно отображает даже пустые ячейки - это неудобно, и хочется именно диапазон из конкретного места, а не просто уровни с 1 по 20. Следующий диапазон находится на 6 столбцов правее. Допустим, я вручную прописал 5 строчек с уровнями вниз, логика видна, но если начать протягивать формулу, то Excel абсолютно не понимает, что там за логика и внедряет свою. Как автоматизировать растягивание даже со смещением - я не смог понять

Вторая проблема - это ВПР в умном списке. Сейчас 1 ячейка с динамическими данными, привязанными на уровень, выглядит так - =ВПР([@lvl];СМЕЩ(Данные!$N$2:$Q$16; 0; (СТРОКА(A2)-2)*6); 2; ЛОЖЬ)
Сначала не было смещения, а, как и сверху, просто указан диапазон, повторён 5 раз, но при растягивании применялась стандартная логика сдвига строчки вниз и всё. Excel не понял, что нужно указывать каждые 6 столбцов вправо с каждой новой строкой. Тогда  написал формулу для растягивания вниз с ручным смещением вправо на 6 столбцов. Ура! Оно заработало, но только до тех пор, пока я не применил фильтрацию PPM по убыванию - все формулы полетели, список переиначился, а каждая растянутая формула, чётко основывавшаяся на предыдущей, перестала понимать, откуда теперь брать данные


Вот такая последовательность требуется
=ВПР(Таблица[@lvl];Данные!N2:Q16;{2};0)
=ВПР(Таблица[@lvl];Данные!T2:W16;{2};0)
=ВПР(Таблица[@lvl];Данные!Z2:AC16;{2};0)
=ВПР(Таблица[@lvl];Данные!AF2:AI16;{2};0)
=ВПР(Таблица[@lvl];Данные!AL2:AO16;{2};0)
итд

Прописав вручную ячейки, растянув вниз применилась логика N3:Q17 N4:Q18 итд, когда нужно было смещение на 6 столбов вправо, но с чётким указанием диапазона, чтобы каждая ячейка знала, к чему отсылаться при разном расположении в строках таблицы

Буду рад любой помощи - от идеи по переделыванию изначального вида Данных, чтобы проще было создать таблицу, до буквальных кликов, как растянуть проверку данных и формулу ВПР. Приложил поломанную таблицу, если я плохо всё объяснил

Версия 2108, Microsoft Office LTSC профессиональный плюс 2021