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 🤣🐐.
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:
If the user selects ‘one’ in say cell A2, then cell B2 will have a corresponding set of values that we will make:
However, if the user selects ‘two’ in cell A2, then B2 will have a different set of values such as:
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:
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:
- Create a new sheet tab called Notes.
- Add a list of all the selected items in Column A and Column B.
- Set up our Column A dropdown back in Main.
- Reference Column A, Main and recreate individual selections for each row that we will hide in Notes.
- Update column B in Main drop-down data validation and change their relative and absolute values.
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:
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.
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)
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’.
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:
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.
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:B12. Right-click > select Data validation.
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:
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.
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.
Want a fast track to boost your Spreadsheet efficiency? Grab one of these handy Google Sheets Shortcuts mousepads that I created from my store.