Archer Dynamics

Problem

Ever watch a dropdown list break because someone added a new option to row 51? Yeah, me too. And if you were handy enough to make an offline reference that dropdown menu was reading, added more values to it without updating the reference range, and the dropdown was not refreshing? Yeah, did that one too. Here’s the fix that took me from “update every dropdown manually” to “it just works.”

How to solve this

The setup is stupidly simple. Suppose you have a sheet with a list of items you want to make to become options in your dropdown

- Convert your list to a Table (Ctrl+T)
- Name that table something sensible like ProjectTypes
- Go to >> Table Design >> Properties
- Here is the stumbling block: 
  Excel does not like using tables as data validation sources
- We have to find a workaround by making that table a 
  Named Range
- Highlight all the cells in the Category table
  
  Note:
  Not the header, but highlight all rows in that column
  
- Go to Menu >> Formulas >> Define Names >> Define Name
- Point your dropdown to =ProjectTypes tables
- Give this range a name: ProjTypes - now you have a named range
- Now go to your table where you want to create dropdown menus
- Highlight the cell range >> Menu >> Data >> Validation
- In the Allow section select List
- In the Source type equal sign and the name of your Named Range
  =ProjTypes
- Press OK to submit
- Done

Now you have a drop down menu in Excel and the choices available in that dropdown will match what is in that Project Types reference table – as you keep adding or removing values in that table, the drop down choices will update accordingly. That’s it. No OFFSET formulas, no COUNTA gymnastics.

The real magic? Cascading dropdowns. Table Categories feeds the first dropdown. Table Subcategories (filtered by FILTER function) feeds the second. Zero maintenance, infinite scalability.

Stop maintaining dropdown ranges by hand. Let Excel do the work.

Need Help?

Need help streamlining your processes or solving tricky business problems? I offer one-on-one consultations to get you unstuck fast. Book a free consultation with me today at goarcherdynamics.com.

Want more practical tips and workflow hacks? I publish them regularly on my blog — check it out and subscribe for newsletter updates: goarcherdynamics.com