Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice (Updated 28 Mar 2022)

Update a Dropdown List Dynamically Across a Range in Google Sheets

I don’t often do this*, but I recently got a question on my YouTube tutorial, Update dropdown list in Google Sheets dynamically based on previous dropdown choice: Data Validation, about whether or not this process can be applied to a column range.

The short answer is yes. The long answer is that it is a bit ugly, but it works.

Let’s first clarify the problem.

*obviously not procrastinating before starting another big project ??.

The problem

Let’s say in column A of our Google Sheet tab called Main, we want a dropdown menu for each cell in your column from, say A2:A12. We’ll keep it simple and make our options:

  • one
  • two
  • three

If the user selects ‘one’ in say cell A2, then cell B2 will have a corresponding set of values that we will make:

  • one-A
  • one-B
  • one-C

However, if the user selects ‘two’ in  cell A2, then B2 will have a different set of values such as:

  • two-A
  • two-B
  • two-C

We also want the same scenario to occur for each row. So the user can select a value in any row in column A and that will update the corresponding Google Sheets data-validation options in column B.

So our range might look like this:

Google Sheets Data Validation dropdown dynamically changing across a range.
Click to Expand!

The solution

So how did we achieve this? Well, just like in the original tutorial we relied on another Google Sheet tab that, in this example, I have labelled, Notes.

To achieve this dynamic dropdown in column B we need to do a number of steps:

  1. Create a new sheet tab called Notes.
  2. Add a list of all the selected items in Column A and Column B.
  3. Set up our Column A dropdown back in Main.
  4. Reference Column A, Main and recreate individual selections for each row that we will hide in Notes.
  5. Update column B in Main drop-down data validation and change their relative and absolute values.
Hire a Google Workspace Developer for your Business Needs

Setting up the dropdown data for Columns A & B

Go ahead and create a new Google Sheet tab and call it Notes. In cell A1, add the header Option 1 and column B1 the header Option 2. Then from column A2 down type in three sets of each of the following:

  • one
  • two
  • three

Finally, from B2 down type in the corresponding number followed by A, B and then C for each number set so that it looks like this.

create a list of selection items for a dropdown data validation in Google Sheets
Click to Expand!

Set up the first dropdown data validation

Head back to the Main Google Sheet tab Give A1 the header Option 1.

Next, select the range from, say A2:A12 and then right-click > select Data Validation.

Check that you have selected the correct range (1).

Then ensure the Criteria is set to List from a range. From here, click the little grid symbol and navigate to your Note sheet tab and select the range from A2:A10 encompassing all your options (2).

Ensure that the dropdown menu is selected (3). And then hit the Save button (4)

Google Sheets Data Validation dialogue for dropdown menu

Creating column B options specific to each row of Column A selections

Navigate back to our Notes sheet tab. We need to create a new list of column B options for each row of data from column A in our Main sheet tab.

In cell E1 type the header, ‘Cell Ref’. Then in F1, add the header, ‘Selections for column B Sheet 2’.

?You can support me for free by using this Amazon affiliate link in your next tech purchase :Computers & Stuff! ?

Next, let’s just add a range of titles to Notes column E indicating each row that we will reference back in Main. Type ‘A2′ to cell E2, ‘A3’ to cell E3, and so on down the column until you get to ‘A12’ in cell E12. This column is just a description too and has no formulaic effect on the actual process. It is just a handy guide.

a unique range of values for each associated cell

Column F is where the real magic happens. We need to create a corresponding list of values for each cell in Column B of the Main sheet tab that changes depending on the choice from column A of the Main sheet tab.

Here is the formula for cell B2 of Notes.

=IF(Main!A2=“”,“”,TRANSPOSE(FILTER($B$2:$B$10,$A$2:$A$10 = Main!A2)))

Go ahead and drag it down the column. You can drag it past the last item in the column just in case there are more cells added to Main later.

You can now head back to your Main sheet table and make a change to your selection and see how it affects the values in column B.

Let’s look at how this formula works:

Filtering out only those items with a corresponding selection in Col A of the adjacent row.

First, we need to filter out all the items in our selection range in column B of our Notes. The FILTER function first takes the range you want to display as its first argument. For us, this is $B$2:$B$10. Note the dollar signs beside the column letter and row numbers. This forces those rows and columns to be locked in so when you move them down or across other cells their values won’t change. The values are absolute. More on this here:

https://yagisanatode.com/2017/11/01/how-do-i-lock-certain-cells-in-a-formula-in-google-sheets/

The next argument is the range that we want to use to filter our data and how we want it filtered. In our example, we are using $A$2:$A$10 of Notes. We only want those values in column B where column A is equal to the user’s selection in Column A of the Main sheet tab.

This filter will provide us with a vertical list of values.

Making our values run horizontally

We need to now make our values run horizontally so that they can be references in each cell.

This is achieved with the TRANSPOSE function.

Future-proofing

We will future-proof our list of formulas so that we can drag it down the column a ways should the user add more cells in Main. We don’t want to display any errors if there is a blank cell in Column B of Main so we can use an IF function here to hide it by saying if Main A2 is blank then we just want to display blank otherwise we want to run our formula.

Setting up the column B dropdown

Head back to the Main Google Sheet tab. Select the range B2:B12Right-click > select Data validation. 

Google Sheets Data Validation dialogue for dropdown menu unlock some absolute reference
Click to Expand!

Ensure your range is selected (1) and the Criteria is set to List from a range (2). Select the grid to update the range and head over to the Notes sheet tab.

This time we are going to select from Cell F2 across to Cell M2. You might have noticed that we have select across our columns far more than what options we have. This is just to make sure that if we add more options in the future, then we have them covered.

Now that you have your selection you will need to make some modifications to it. Have a closer look at our selection in the image:

=Notes!$F2:$M2

You can see here that we are only locking in (making absolute) the columns, but not the rows. This will make the rows relative to each cell as the data validation dropdown goes down the row. So our dropdown in cell B3 will reference Notes!$F3:$M3 and so on down the column.

To wrap up the data validation dialogue. Ensure that you have selected Show dropdown list in cell and hit save.

You are all done. Go ahead and give it a try.

The video

Wrap up

You can now go ahead and right-click the Notes tab and select Hide sheet so no one can see your working.

As you can see, this is a pretty messy process, but it is effective. Once you set it up, it pretty much maintains itself.

Here is a link to the Google Sheet. Go to File > Make a copy to grab your own copy of the sheet to play around with.

The Google Sheet

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.

 
Google Sheets Shortcuts Mousepad
Google Sheets Shortcuts Mousepad
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.
Google Sheets Shortcuts Mousepad Gel
Google Sheets Shortcuts Mousepad Ge

~Yagi

Bonus 1 – A dropdown list family with options that are removed after each selection.

Boy am I being nice here. This little bonus comes from another commenter question.

The OP wanted to have a row of 4 selections, say A, B, C and D. When one selection is made (say “B” is selected), then the next option only has the three remaining options to choose from ( A, C and D). The third dropdown would have 2 options remaining and the last one would have one.

Check out the solution here to see how it works in action:

Solution Google Sheet. 

The helper sheet

This time in our helper sheet tab we first create a list from A2:A5 with our main options A, B, C and D. This will always be our option 1 choice.

Google Sheets Data Validation Drop Down Dynamic Reduced Choice_All Choices List

Next, we make 3 column groups that each 4 columns across.

Google Sheets Data Validation Drop Down Dynamic Reduced Choice_3 col sets of 4 rows each

In cell C2 we add the following formula:

=TRANSPOSE(FILTER($A$2:$A$5,$A$2:$A$5 <> Options!A2))

This formula filters out any option from our main options list that appears in our ‘Options’ sheet tab in A2 and transposes it to run horizontally to make it easy to add other options.

In cell G2 we add the formula:

=FILTER(C2:F2,C2:F2 <> Options!B2)

This looks at the option in B2 of the ‘Options’ sheet tab and then removes these options from the remaining options in range C2:F2.

We do the same again in cell K2.

=FILTER(G2:J2,G2:J2 <> Options!C2)

We then select range C2:N2 and drag it down the sheet for as long as you might think you may need it in your main sheet.

The main sheet

For our first column A our data validation will be simply to select the range of our full list on our helper sheet.

='Dropdown Helper'!$A$2:$A$5

For Cols B, C and D we need to select our 3 options rows.

  • Col B – ='Dropdown Helper'!$C2:$F2
  • Col C – ='Dropdown Helper'!$G2:$J2
  • Col D – ='Dropdown Helper'!$K2:$N2

If you have found the tutorial helpful, why not shout me a coffee ☕? I'd really appreciate it.

The Video

Schedules release  data: 13 Apr 2022

Bonus 2 – Any dropdown in a row with display remaining dropdown options

Okay, I am just going bonkers with freebies today.

In this example, we have our list of options A, B, C and D. If say on row 2, we select perhaps option C from our drop-down in cell B2, then A2, C2 and D2 now have only the remaining options A, B and D. If we chose option  in cell D2, then our remaining empty cells A2 and C2 can now only select from options B and D.

Check out the example in sheet tab, “Start at any col”, of the Google Sheet:

Dropdown list family with diminishing options

Google Sheets Data Validation Drop Down Dynamic Reduced Choice_Any cell in a row

Of course, you can put Cols E through L above in a separate sheet tab and hide it from your users.

Also, keep in mind that one of the downsides of this approach is that you will get the little red warning triangles in the top right of each cell you add a selection as it is removed from the data validation choices dynamically.

Add all selections.

First, add all of your selections options for your drop down. We did this here in column F.

Create and Publish Google Workspace Add-ons with Apps Script Course 300px

Working our the remaining options for each row.

Next, we need to find out what options are remaining after each selection in each row.

We can use the Google Sheets MATCH function to see if any of our original list of values has been used in our current row of options. If there isn’t a match or ISNA, then we want to display that value only in our H#:K# columns corresponding to the selected row with our FILTER funciton. To check all values in our original drowdown list we use the ARRAYFORMULA function.

Finally, we need to TRANSPOSE our new list so that it runs across our corresponding row.

Add the dropdown data validation

Now we can head over to our rows of dropdowns and apply the data validation.

Select the entire range, in our case A2:D9. Then go to Data > Data Validation. 

For the ‘list from a range’ criteria add the first row in the selection, add the first range =$H2:$K2. Note that I have only made H and K absolute ranges (locked in) and allowed the rows to change.

All done.

 

The Video

Schedules release  data: 21 Apr 2022

Changelog

  • 28 Mar 2022 – Added bonus chapter on dropdown list family with diminishing options

2 thoughts on “Update a range of dropdown lists in a Google Sheet dynamically based on a corresponding dropdown choice (Updated 28 Mar 2022)”

  1. By the way, is there a way to automatically sort the dropdown list in Google sheets without pre-sorting the source list? Thank you.

Leave a Reply