Create dynamic dropdown data validation for multiple rows with Google Sheets

Question

I have a spreadsheet where users are to input data on a number of fields, across some of the sheet's columns. I would like for other columns to have dropdown lists in which options change according to what the user has written in previous columns.

As an example, let's say that I have a workbook with two sheets: 'INPUT' and 'AUX'. In column A (Country) of 'INPUT', the user has to chose a country from a dropdown list. I would like for the dropdown in column B (City) to update and show me only a list of cities in the country the user has inputted in column A. I would like this to happen in more than one row.

Column A of 'INPUT' is drawing, through data validation, from a list of countries which is in column A of 'AUX'.

I can kind of do what I want using a Filter function. However, (1) this does not give me a dropdown and (2) whenever there is more than one city in a country, the list of cities, goes through to the next row and prevents the use of the spreadsheet.

I have built a sample spreadsheet which is available here: https://docs.google.com/spreadsheets/d/1SPXIC4qkXRNcWNsja-_MPSjNyYlYD-1PpZ7wdl8Z098/edit?usp=sharing

In the sheet you can see the filter function at work and why it does not solve the problem. Thank you.

Answer 1

A few months ago I needed to do this exact thing in a spreadsheet for a work project and like you I couldn't find ANYTHING about how to do it- but I got crafty. I’ve wanted to answer one of the unanswered questions I had found sooner, but I have struggled to explain my solution without needing to show the confidential data I used in my work project. Seeing the sample spreadsheet you provided helped me take it and work my solution into it- here is a link to my spreadsheet with “dynamic data validation”:

https://docs.google.com/spreadsheets/d/1TFQm1SObvycIQlzgT7MA-dprmK-g7ps7OZsNQVuhc8E/edit?usp=sharing

The problem is that for a drop-down menu style data validation to be dynamic it needs to be referring to a range, but if you want that range to be dynamically updating based off of another value selected from another data validation in a separate column in a way that you could keep working down the rows and have it still be dynamic one must wonder where to put that range for the second column of data validations to be referring to without it getting in the way of new or old rows.

To refer to the spreadsheet I linked above, I did this by making another tab called "CALC" to temporarily store that calculated range. (I normally protect and hide these types of sheets in a workbook, but left it unhidden here to be seen.) On the INPUT sheet, after you select a country from the drop-down menu in Column B, you want the drop-down menu in Column C to auto-populate with the cities that correspond with the selected country.

An INDEX/MATCH formula in that CALC sheet would get the job done by using the Country in the bottom row of its column in the INPUT sheet, but I first needed to figure out how to get a formula to check for that.

I eventually made the following formula that, if placed in INPUT!A2 and copied down the remaining cells of the column, enables detection of the bottom row:

=(IF((B2=""),(""),(IF((ROW(A2)=2),(1),(MAX((INDIRECT("A2:A"&ROW(A1),1)))+1)))))

The above formula will leave the cell blank if there is no country selected in the column to its right, but if there is it will assign an ID number 1 higher than the previous ID number to ensure that the most recent entry, the bottom-most entry, has the max ID number in column A. To avoid circular logic while calculating that number, the INDIRECT formula must be used as shown where it finds the max number in its own column between itself and the first non-header row- which in this case is row 2. Given that, the IF is also used for the occasion that you're working in that first non-header row.

Then, I prepped my "Docking Bay", the reference for the “dynamic data validation” for the INPUT sheet, in the CALC sheet with the following INDEX/MATCH formula in CALC!B2

=(QUERY({AUX!A:B},"select Col2 where Col1='"&(INDEX((INPUT!1:501),(MATCH((MAX(INPUT!A:A)),(INPUT!A:A))),(2)))&"' order by Col2"))

Finally, I set the data validations for INPUT!C2:C to reference CALC!B2:B and then you have a dynamically updating drop-down style data validation!

However, an important note about this solution is that the "dynamic data validations" will only be working correctly for the row with the highest ID number in the ID column. That means you won't be able to easily edit old rows or fill in skipped rows.