Get the list from a table headers drop down

[nemmi69]

Instead of reinventing the wheel is there a way to get the unique list that shows in a headers drop down?

 

[Herakles]

Do you mean the list in the filter selection box as shown?

If so then look at the UNIQUE function. The following when set as a formula in a cell will
give you a list of unique values in the given range.

=UNIQUE(DataTest!D2:D13,FALSE,FALSE)

Use the sort function to sort the list.

=SORT(UNIQUE(DataTest!D2:D13,FALSE,FALSE))

These will work in 365.

 

[nemmi69]

Yes its the filtered list in the headers dropdown

 

[Herakles]

So what do you want to do with this unique list?

 

[jdellasala]

Although one might assume table headers would be unique, and they MUST be unique for an Excel Table, it's easy enough to get a unique list of any range using the UNIQUE function. When I use it, I typically using with SORT to get a sorted list of unique values as in SORT(UNIQUE(range)). In the case of an Excel Table's headers, the formula would be

=SORT(UNIQUE(TableName[#Headers]))

That said, if you need a list for a Data Validation list, that function will automatically produce a unique list of values of any range. Unfortunately there is (currently) no Sort option for that kind of Data Validation list. I have posted a request to add the option at the Excel Feedback site. If you think that's a good idea, please go here and give it a vote! Microsoft monitors the votes to determine features to be added, and right now it has only my vote !

 

[nemmi69]

It is not the table headers that have to be unique it is the list that they contain in their dropdown.

I want to use them to build a report

 

[Herakles]

So where do you want to put this unique list of values in the cells below the header?

 

[nemmi69]

In a variable to build a report against

 

[jdellasala]

In a variable to build a report against

Formulas needed noted above. The results of the formulas will run down a column. If you need the results to run across a row, simply use TRANSPOSE to run the list across instead of down:

=TRANSPOSE(SORT(UNIQUE(TableName[#Headers])))

SORT is not needed if you need to maintain whatever order the source is.

 

[nemmi69]

Doesnt that just return the headers?