Here’s how to make a pull-down list in Excel when the source table has multiple entries.
We’ve already explained the basics of making a drop-down or pull-down list in Excel based on a column in a table. Sometimes the source data has more than one entry for each item. An order list will have the customer name or product info multiple times.
For example, a list that has the same name many times. You don’t want a pull-down list to show two Harpos and two Grouchos.
The solution in Excel 365 & Excel 2021 is Unique(), the dynamic array function to return a list of values with each appearing only once. The same problem as in other data lists applies; Excel won’t accept a dynamic array as a data validation list. But the solution is quite different.
What’s needed is to make a unique list in a data sheet then link to that list in a special way that will expand/contract as the table changes.
Make a unique list from a table with a function like =UNIQUE(MarxOrders[Customer])
Now link to that dynamic ‘spill’ list in a special way. The list source references the first cell of the unique list followed by a hash # e.g. =$F$10#
The hash tells Excel to return all the cells in the spill array, starting at the cell given and working down until the dynamic array ends.
And now there’s a pull-down list with each name just once.
Come on Microsoft!
All this messing about with named ranges or separate lists should not be necessary.
Microsoft should update the Data Validation feature to directly accept table references and dynamic arrays. In a supposedly mature product, clumsy workarounds are a disgrace.