динамический выпадающий список – возвращаемое значение

У меня есть таблица с тремя столбцами. Это имя, возраст, ценность

Я хочу создать раскрывающийся список incell на основе уникальных значений в столбце «name». В столбце «имя» могут быть две ячейки с «Джоном» в них, например. т.е. дубликаты

Когда в раскрывающемся списке выбран «Джон», я хочу создать еще одно динамически созданное выпадающее меню, которое отображает только возраст для записей. Например, если в столбце имен были две строки с «Джоном», тогда в раскрывающемся списке должны отображаться только возрастные значения для каждого из них. На основе двух идентификаторов выбора, чтобы показать, что находится в столбце value.

Как вы думаете, мне нужно использовать макросы для достижения этого?

Чтобы усложнить ситуацию, количество строк в списке может меняться со временем, и поэтому пустые имена нужно будет удалить. «Игнорировать пробелы» при создании всплывающего окна incell ничего не делает, и они все еще отображаются в списке.

Любой совет будет принят во внимание.

Основываясь на вашем описании, я думаю, что вы создаете список проверенных выпадающих ячеек. Списки могут быть подмножествами исходной базы данных. Пример такой базы данных может выглядеть так:

Первоначальный список

С проверенными падающими смещениями, такими как:

С Drop Downs

Это может быть достигнуто за несколько шагов.

1) Создайте подмножество всех уникальных имен в столбце помощника, я сделаю это в J.

=IFERROR(IF(NOT(INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($J$1:J1,$A$2:$A$20),0,0),0))=""),INDEX($A$2:$A$20,MATCH(0,INDEX(COUNTIF($J$1:J1,$A$2:$A$20),0,0),0)),NA()),"") 

Диапазон, который я тестирую для uniques, распространяется только на A20, но вы можете принять это, насколько вам нравится.

2) Поскольку вы делаете это с помощью списков проверки, которые пользователь должен выбрать, вы должны, вероятно, их алфавит. Я делаю это в следующей колонке над К.

 =IFERROR(INDEX(INDIRECT("J1:J"&SUMPRODUCT(--(LEN(J:J)>0))+1),MATCH(ROWS($J$2:J2),COUNTIF(INDIRECT("J1:J"&SUMPRODUCT(--(LEN(J:J)>0))+1),"<="&INDIRECT("J1:J"&SUMPRODUCT(--(LEN(J:J)>0))+1)),0)),"") 

Эта формула является формулой массива и должна вводиться с помощью ctrl + shift + enter для первой ячейки, а затем вы можете заполнить ее. В непосредственной близости от клетки давайте следить за полным диапазоном, занимаемым значимыми именами, используемыми для составления нашего алфавитного списка (без пробелов). Введите следующее:

 ="K2:K"&SUMPRODUCT(--(LEN(J:J)>0))+1 

Обратите внимание, что это предполагает, что мой uniques находится в столбце J. Если вы разместите их в другом месте, вам нужно будет изменить это.

3) На этом этапе мы можем сделать первый подтвержденный снимок. Я использую excel 2016, поэтому я выбираю ячейку, в которой я хочу выпадаю (в моем случае E2), а затем перейдите в Data -> Validation. В этом разделе я выбираю Allow: List; и источник: я ввожу следующее:

 =INDIRECT($K$1) 

Поскольку K1 – это ячейка, в которой я отслеживаю диапазон полезных и алфавитных разделов. Пример: Проверка

Теперь вернемся к основному листу, у меня есть раскрывающийся список в E2 с этим списком. Затем построим подмножество полезных веков.

4) В новом вспомогательном диапазоне (для меня «M2: M10») введите следующее:

 =IFERROR(SUM(LARGE(IF(($A$2:$A$20=$E$2)*ROW($A$2:$A$20),$B$2:$B$20,""),COUNTIF($A$2:$A$20,"="&$E$2)-ROW()+ROW($M$2))),"") 

Это еще одна формула массива и должна быть отправлена ​​с помощью ctrl + shift + enter , но, в отличие от другой формулы массива, вы должны ввести ее для всего диапазона сразу, я просто выбрал небольшое количество ячеек, потому что знаю, что у меня не будет более чем на несколько веков. Если вы подозреваете, что у вас будет больше сделать этот диапазон большим.

Точно так же, как имена позволяют отслеживать диапазон полезных возрастов в соседней ячейке (я выбрал N1).

 ="M2:M"&COUNTIF((M:M),">0")+1 

5) Сделайте следующее подтвержденное падение. Выберите ячейку (я выбрал F2) и снова Data-> Validation, выберите список и теперь использую источник возраста:

 =INDIRECT($N$1) 

6) Получение ценности, которая соответствует индивидууму с этим возрастом, теперь легко. В следующей ячейке от enter:

 =SUM(IF((A2:A20=E2)*(B2:B20=F2),C2:C20)) 

Там у вас это есть. Пример того, как выглядит мой заполненный лист, приведен здесь:

Полностью функциональный

Последнее, что нужно упомянуть. Когда вы сделаете это и переключитесь на новое имя, оно не будет автоматически сбрасывать возраст. Поэтому, чтобы сделать это сброшенным, вы можете использовать некоторое умное условное форматирование. Я отформатировал F2 & G2 со следующим правилом и установил цвет шрифта в белый цвет:

Условный формат

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

Удачи!

  • Excel DSUM с несколькими критериями
  • Результат вывода к точному числу
  • Как мы помещаем точки маркера в ячейки листа excel?
  • Проблема Excel COUNTIF
  • Соответствие данных из двух разных таблиц Excel
  • Формула Excel, ЕСЛИ определенный день недели, затем добавьте «x» количество дней
  • Как хранить данные Excel в Firebase?
  • Я хочу отправить файл вложения с помощью Excel Macro
  • Гиперссылки в excel не работают
  • Как преобразовать тип данных Exponent в числовой формат в Excel.?
  • Удаление ведущих нулей в Find and Replace
  • Давайте будем гением компьютера.