TLB Tip 007- Automatic Dropdown Menus

There's always so much magic with Google Sheets! You can automate almost anything, including your dropdown menus. Suppose we have a long list of to do list task items that we want to categorize. We could try to create all of our categories in advance and set up a dropdown with the options. But, inevitably, we'll want to add to the dropdown overtime, and this can be a pain. We have to edit it and then update all the other dropdowns too.

But, there is a way around this. You can use the Dropdown (from a range) option. And then, we can build a range with a formula that gets all the unique values from our dropdown column.

...don't worry if this doesn't make sense. Let me show you...

Set up the dropdowns like this, based on the empty range in D2:D

And then in cell D2, use this formula to build the list of options for the dropdown:

=SORT(UNIQUE(B2:B))

which looks like this in our Sheet:

Based on my table these are my ranges. Please adjust your ranges in the dropdown and formula if you're using different rows and/or columns. 

Initially, both the dropdown and the "allowed list" are empty, because they are based off of each other. This is where we need to jump in and start categorizing our data. We have to add the first value. In the first dropdown cell, type in the first category e.g. "Print". It shows up immediately in all the dropdowns!

That's the power of this method. On any dropdown cell, we can either choose from the available dropdowns or simply type a new category and it gets automatically added to the global list. Here you can see how it works in practice: