Dynamic drop down list based on table elements - possible???

**toblju** · 09-13-2013, 05:40 PM

I love working with (defined) tables connected to pivot tables. I also use GETPIVOTDATA to create dynamic reports with nice layouts. Sometimes I have drop downs list (Data validation) for choosing product, year, customer etc.

But is it really possible in Excel (2010 or 2013) to create a drop down list (data validation) that show unique items from a field in my (defined) table?

Please, I really, really need this feature

Tobias

**FDibbins** · 09-13-2013, 06:29 PM

I woud suggest making a separate list that will excude duplicates, and then making the D/D refer to that. Something like this will pull out unique entries from B2:B20, into C2, copied down...

=INDEX($B$2:$B$20,MATCH(0,INDEX(COUNTIF($C$1:C2,$B$2:$B$20),0,0),0))

**toblju** · 09-14-2013, 07:05 AM

Thanx!
I did use table reference instead of $B$2:$B$20 and then created a named range with OFFSET-function connected to the unique list and then used that named range in my data validation.
Thanks again for you input!
/Tobias.