Динамическая проверка данных в Excel

Question

На моем первом листе у меня есть все мои варианты:

A        B
=============
yellow   DOG

Green    DOG

PINK     DOG

YELLOW   CAT

Pink     CAT

black    BIRD

RED      BIRD

На моем следующем листе я хочу:

Animal     Choice of color
===========================================================
DOG        Data drop down box listing YELLOW Green and Pink

CAT        Data drop down box listing YELLOW and Pink

BIRD       Data drop down box listing black and RED

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

Можно ли сделать так, чтобы на моем втором листе отображались только отдельные значения из столбца A листа 1 и отличные значения из столбца B листа 1, где sheet2 Column A = sheet1 Column A?

Итак, если бы у меня был лист 1:

dog   red

cat   blue

dog   red

fish  purple

dog   yellow

cat   yellow

fish  green

лист 2 будет:

A     B
==============================
dog   drop down: red, yellow
cat   drop down: blue, yellow
fish  drop down: purple, green 

Обратите внимание, что цвета отображаются в качестве параметров, только если они соответствуют животному, для которого они предназначены.

Answer 1

Мое решение состояло в том, чтобы создать третий столбец, который отображал только правильные варианты.

Лист1

     A       B                C
1  yellow   DOG   =IF(Sheet2!$B$1=B1,A1,"")
2  Green    DOG   =IF(Sheet2!$B$1=B2,A2,"")
3  PINK     DOG   =IF(Sheet2!$B$1=B3,A3,"")
4  YELLOW   CAT   =IF(Sheet2!$B$1=B4,A4,"")
5  Pink     CAT   =IF(Sheet2!$B$1=B5,A5,"")
6  black    BIRD  =IF(Sheet2!$B$1=B6,A6,"")
7  RED      BIRD  =IF(Sheet2!$B$1=B7,A7,"")

Sheet2

     A       B
1  Animal?  CAT
2  Colour?  ...

Вы можете заменить ... списком проверки данных, взятым из Sheet1!B1:B7 . Он будет содержать пустые опции, но это самое близкое к тому, что я нашел.

Если вы хотите сделать формулу аккуратнее, назовите ячейку Sheet2!B1 как Animal и используйте этот термин в формулах, например =IF(Animal=B1,A1,"") . Это делает их намного проще для чтения.

Answer 2

@ Энди, посмотри ниже. Я взял ваш 2-й набор данных (у вас есть 2 разных набора в операторе), который не был отсортирован, и отсортировал его в отдельной копии сбоку в столбцах G и H.

Затем я удалил дубликаты («собака, красный» повторяется).

Затем я создал именованный диапазон "AnimalsSorted", как показано на следующем рисунке:

Затем на Листе 2 я смог создать валидацию данных для каждой ячейки в столбце B. Источником проверки данных для каждой ячейки является "список", формулы которого приведены для ясности в столбце D:

Похоже, это должно работать для вас; хорошая охота!