If you’re working in Microsoft Excel and want a drop-down list from a dynamic array you have to use a workaround because the direct method for fixed arrays doesn’t work for dynamic arrays. Here’s the simple workaround to use a dynamic array function to generate a list of unique values from a data table, and then reference that “spill” range to power your drop-down menu without showing repeats.
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.
This works with Excel 365, Excel 2021 or Excel 2024 becuase it uses the UNIQUE() function.
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.
Unique() with a twist
The solution in Excel 365 & Excel 2021 and later 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])
Link drop-down list to dynamic array
Now link to that dynamic ‘spill’ list in a special way. The Data Validation | List source won’t accept a formula that returns a dynamic array (Grrrrr) so you need to trick Excel with a ‘two-step’ workaround:
- Make the dynamic array of the menu items, including the Unique() function.
- That array can be anywhere ‘hidden’ out-of-sight on the workbook.
- Point the List source to the first cell of that array with a # symbol at the end.
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.
