Got Sheet is brought to you by beehiiv. If you’re considering starting a newsletter, check them out for 30 days free and 20% off your first 3 months…try beehiiv now.

From the Headlines…

Last week

• In the last issue…we looked at another dropdown list topic - multiple selections. Check that out here.

MAIN ARTICLE

We all know dropdown lists, right? Well, what if we wanted to have the options in our dropdown change when other selections change?

We can do this in three easy steps:

1. setup named ranges

2. use INDIRECT()

3. add some optional Apps Script

Video walkthrough:

Named Ranges

Everything hinges on setting up proper named ranges.

First things first: we need to setup tables (or just arrays) for all of our options.

In our example sheet, I’m using three variables: type, color and size.

For each type of item, there are different color options, and for each color, there are different size options.

So, I’ll setup a Types table:

A Color table:

And a Sizes table:

The setup takes a minute, but it allows each piece to be dynamic in our dropdown lists.

Our first dropdown for the type is straightforward. This one doesn’t change so we simply reference our three types from the first table.

But now, we need for our color selection to dynamically update based on the type we selected. And for this, we’ll use INDIRECT()…

The INDIRECT() function

The indirect function returns a cell reference specified by a string.

Translation: if we have the string “rollerblade” in cell A2, then by using the function INDIRECT(A2), it will return the values of the named range “rollerblade”.

So, for each of our types, we specify named ranges corresponding to that type’s available colors.

In the same way, we setup named ranges named after the colors that correspond to the sizes available for each of those colors.

Then, we create a new table called Combos which updates whenever we make our selections by using INDIRECT() in both columns.

Finally, up at our original selections table, we update the dropdown list rules so that they pull from that Combos table for both the color - Combos[color] - and the size - Combos[size].

Lost? Watch the video and it will make sense. I promise it’s not complicated, it just takes a second to wrap your head around.

Apps Script Bonus

Everything works fine without Apps Script, and this is why I love this project.

However, there is one minor issue. When we change one of the dropdown lists, after selecting something in all of them, it’s possible for an error to occur. For instance, if I change from rollerblade to skateboard, the color I’d selected for rollerblade should no longer be available, but it’s still there.

Google Sheets will flag this, but it won’t delete the value.

To have the selections clear after each new selection, we can write some Apps Script to do it for us…

function onOpen(){
  let sheet = SpreadsheetApp.getActive()
  sheet.getRangeByName("selection").clearContent() 
}
function onEdit(e) {
  let sheet = SpreadsheetApp.getActive()
  let selection = SpreadsheetApp.getActiveSpreadsheet().getSelection();
  // Returns the current highlighted cell in the one of the active ranges.
  let currentCell = selection.getCurrentCell();
  let editedCell = currentCell.getA1Notation();

    if (editedCell === "A2") {
      sheet.getRange("B2:C2").clearContent();
    } else if (editedCell === "B2") {
      sheet.getRange("C2").clearContent();
    }
  }

In the first function, onOpen(), we clear all three dropdown lists as soon as we open the spreadsheet.

In the second, we clear B2:C2 anytime we edit A2, and we clear just C2 anytime we edit B2.

Wrap up

Hope this is useful for you. If it was, please let me know and subscribe to my YouTube channel.

NEXT STEPS

Whenever you’re ready, here’s how I can help:

1. Business tech stack (FREE)
   My recommendations for software and tools I use to create content and run my online business.

2. Sponsor Got Sheet
   Got Sheet teaches business operators, teachers and entrepreneurs how to get good at spreadsheets. Interested in sponsoring an issue or a series of Got Sheet newsletters? Reach out to get more information.

3. Personal budget tool
   As a Got Sheet subscriber, I want you to have a 75% discount on the personal budget I built 10+ years ago and am still using to this day.

Happy Spreadsheeting!

Enjoyed this issue? Subscribe below or Leave a testimonial

Cheers, Eamonn
Connect on LinkedIn, YouTube & Twitter.