Cascading data validation lists that only displays unique entires.

[Another Jon]

Here is a gross over-simplification of my dataset in Excel (it's larger and more purposeful than this). The data is in a table, with quite a few more columns and vastly more rows.

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">**Type**,**Creator**,**Name**,**Characteristic**
Animal,Frankenstein,Cat,Aloof
Animal,Frankenstein,Dog,Awoof
Animal,David Warner,Newt,Slimy
Animal,David Warner,Slug,Slimy
Not_Animal,David Warmer,Laster,Pew-Pew
Not_Animal,David Warner,Computer,Calculon
Not_Animal,No One,Nothingness,Void</code>

Wihin excel, I am trying to make cascading data validation lists that do not repeat options for the user.

I need to present only the unique values in each cascading drop-down list.

For instance, Choice 1 would be "Animal" or "Not_Animal" derived from Type. Animal is represented 4 times in my dataset, Not_Animal 3 times; yet in the drop down, "Animal" and "Not_Animal" should only appear once, each.

Assuming a user chose "Animal", the dependent drop down would present him with "Frankenstein" or "David Warner", both of which have one or more rows associated with "Animal". In this drop-down, again, both "Frankenstein" and "David Warner" would appear once despite being in the data set multiple times. Another choice, "No One", would _not_ appear because it has no associated row with Animal.

Finally, on a third drop-down if he previously "Frankenstein", he could choose "Dog" or "Cat" and would not see "Newt", nor "Slug", because these have no rows associated with "Frankenstein" and "Animal". Neither would the user be presented with "Laser", "Computer" or "Nothingness". Once chosen, I'd autopopulate the rest of the cels which are now inherently based on unique data.

I'm struggling to derive the unique options, Animal or Not_Animal, followed by the subsequent unique data set to present to the user. I've been mostly working with Names and Formulas, and, considering nothing has come close to working, I don't know if it's worth cluttering this message with my abysmal failures.

I can certainly get Type into a pivot table of unique values, and the same for Creator. I can reference Type into a list, but can't seem to get a correlation between Creator to go with it (it comes up empty), using =INDIRECT and simply the name of a function I created.

So, again, I can get cascading data validation drop-down lists to work, but I can't filter out duplicates without it breaking horribly. When it works, I'll have Animal repeated 4 times in a drop down, and Not_Animal repeated 3 times. Ugly but tolerable in this example, but, given a thousand rows with many duplicates, its not practical.

Thanks for any help!

 

[Ingolf]

Cross post here:

https://www.excelforum.com/excel-ge...-validation-lists-with-only-unique-items.html

 

[Another Jon]

Cross post here:

https://www.excelforum.com/excel-ge...-validation-lists-with-only-unique-items.html

Is there something wrong with trying to reach a different audience?

 

[Kyle123]

Not per se, the issue is that you haven't followed the rules in doing so, from the forum rules:

Rule #13 :
We prefer that members do not cross-post questions to other forums, but when this does occur members should do the following:
- Post the details of your question on our forum. Do NOT simply post a link/re-direct to the question in another forum with no details posted here.
- Make it clear that you have cross-posted and provide links to the cross-posts.
Cross-posted questions that do not comply may be deleted or locked. For a discussion on the issues with cross-posting, see this link: https://www.excelguru.ca/content.php?184