Dynamic Dropdown Lists and Skunks

THREE people in the past 24 hours have had DROPDOWN DRAMA! But there are easy solutions. We just need to relax. 


Cheryl maintains clean data, by using dropdown lists to control data entry. But there’s a problem she describes:

“Oh! I hate it when I make dropdowns with 3 items and then somebody asks me to add 3 more. Re-doing all of those lists is a hassle!”

Yes Yes. In comparison, making those dropdown lists all over again makes 2 wild skunks look like fun. I’ve been there. I’ve completely re-created dropdown lists, and it can be especially unnerving when the new entries have to be in a specific order.

Ah! But it’s not so difficult when you use Excel tables to make the lists DYNAMIC.

Josh and Heather (and everyone else), put your glasses on and pay attention. We’re going to address Cheryl’s need for Dynamic Dropdown lists, and your question about Dependent Dropdown lists.

Dynamic Dropdown List: easily add new entries to existing list
Dependent Dropdown List: list is dependent upon variable data in another cell



Can you think of other uses for  Dynamic Dropdown lists?

I automatically make Dynamic Dropdowns because when a spreadsheet is being developed, there is always something unforeseeable that needs to be added or removed:

  • Employee names
  • Cities
  • Shipping methods
  • Menu items
  • Destinations
You can start with a list of: London, Paris, Manila, Denver and then someone remembers that we need to add Montreal and Perth.
So, unless you’re sure you have static choices like Yes & No, it’s good to be able to create Dynamic Dropdown lists and add new entries to the bottom of a table.
How about Dependent Dropdown Lists?
As I point out in the screencast, Dependent dropdown lists do 2 things:
  • They limit choices to the ones that are appropriate
  • They limit the number of choices so that you don’t have of all 18 choices, you have only the handful that you really need.
Ideas for Dependent Dropdown Lists:
  • Foods
    • Fruits, Vegetables, Meat, Desserts, Drinks
  • Routine Tasks
    • Monday, Wednesday, Friday, Saturday
  • Process ownership
    • Marketing, Customer Service, Inside Sales
  • Diets
    • Kosher, Vegetarian, Shellfish Allergy, Gluten free
In the Diets example, if you select Vegetarian, your dropdown list would not include bacon, turkey leg, or lamb; and you won’t have to scroll through a long list to pluck out the Vegetarian options.

I hope this helps. Please do ask any questions, share your thoughts and leave comments on this page. It’d be great to know if this is helpful and what can be done better.

photo credit: marsmet541 via photo pin cc