Maruf
Drop down list is a very effective feature of Google sheets. We use drop down list to choose a specific option from multiple options. Additionally, Google Sheets allows us to create dependent drop down lists. Data entry becomes more accurate and efficient using the dependent dropdown list. In this article, we will demonstrate how to create multi row dynamic dependent dropdown list in Google Sheets with some simple steps & illustrations.
A Sample of Practice Spreadsheet
You can download the practice spreadsheet from the download button below.
Step-by-Step Procedure to Create Multi Row Dynamic Dependent Drop Down List in Google Sheets
In this article, we will show the step-by-step process to create a multi row dependent drop down list in Google Sheets.
Let’s Assume We have a dataset that contains two types of foods (Fruits and Vegetables). We may need to add more items to the Fruits and Vegetables columns. First, we want to create a multi-row dropdown list in the Type column to show Fruits and Vegetables only. Then we need to create a multi row dynamic drop down list in the Product column to show the list of fruits and vegetables depending on the selection in the Type column.
Another concern here is that we need to update the dependent dropdown list whenever more items are added to the dataset. Follow the steps to do it by yourself.
Step 1: Create Named Ranges
- First, you need to create Named ranges to make the formulas more dynamic. So select the range B5:B10.
- Then, from the top menu bar, click on Data. Next, a dropdown list will appear. Now select Named ranges from there.
- This will open a pane titled Named ranges on the right side of the spreadsheet. Then change the default name Named Range to Fruits. In the following box, the range will show your previously selected range DropdownList!B5:B10. Now click on the Done button.
- Then follow the same steps for the Vegetables column or select Add a range at the bottom of the Named Range pane. Next change the name to Vegetables, and select range C5:C10, and click Done.
Read More: Create Multiple Dependent Drop Down List in Google Sheets
Step 2: Apply Data Validation
- Now we need to make the dropdown list in the Type column. For that select cell E4.
- Then select Data from the top menu bar to open the dropdown. Choose Data validation from there.
- After that in the Data Validation dialog box, select List from a range as the criteria. Then enter the cell range (B4:C4) for the dropdown list. Insert the dollar($) sign before the row and the column numbers.
- Then check the Show dropdown list in cell checkbox. Select Reject input for invalid data and click Save.
- After that, you will see an independent dropdown list cell E4.
- You can use the fill handle tool and drag it down to E10 to make a dropdown list in every cell.
Read More: How to Use Data Validation in Google Sheets from Another Sheet
Similar Readings
- Dependent Drop Down List for Entire Column in Google Sheets
- How To Lock Rows In Google Sheets (2 Easy Ways)
- Updating Cell Values Based on Selection in Drop Down List in Google Spreadsheet
Step 3: Prepare Data for Dependent Drop Down List
- Now you need to prepare data for the dependent drop down list. So insert the below formula in cell H5.
=ArrayFormula(IF(LEN(E5:E10),TRANSPOSE(IF(TRANSPOSE(E5:E10)=B4,INDIRECT("Fruits"),IF(TRANSPOSE(E5:E10)=C4,INDIRECT("Vegetables")))),""))- Then it will show the values for the dependent dropdown in cells starting from cell H5.
Formula Breakdown
- LEN(E5:E10): The LEN function counts the length of a string. It will return true if an item is selected in the 1st dropdown.
- TRANSPOSE(E5:E10)=B4: The TRANSPOSE function flips the rows to columns and columns to rows. This will return True if Fruits is selected in the 1st dropdown list.
- INDIRECT(“Fruits”): The INDIRECT function returns a cell reference specified by a string. Here it will return the list of Fruits.
- TRANSPOSE(E5:E10)=C4: It will return true if Vegetables is selected in the 1st dropdown.
- INDIRECT(“Vegetables”): This will return the list of Vegetables.
Read More: How to Edit Data Validation in Google Sheets (With Easy Steps)
Step 4: Create Dynamic Dependent Drop Down List
The last step is to create a multi row dynamic dependent drop-down list in google sheets. For that follow the below-mentioned instructions.
- First, select cell F5 and then go to Data >> Data validation as earlier. Then choose List from a range as the validation criteria. Next, enter H5:W5 as the criteria range and click Save.
- After that, you will see a drop down list in cell F5.
- You can drag the fill handle tool to copy the dropdown to C6:C10. But before that, you have to remove the dollar sign from the data validation criteria range. So go to Data >> Data validation again and change the criteria range from $H$5:$W$5 to H5:W5 by removing the dollar signs. Then click Save.
- Finally, drag down the fill handle icon to copy the dropdown list below.
- After that, you will get your desired multi row dynamic dependent drop down list in Google Sheets.
- If you select Vegetables in the first dropdown list, the dependent dropdown list will show the corresponding values.
- To check if the dropdown is dynamic, insert a new fruit name in cell B8. Then, you will see the new name in the dependent dropdown list too.
Read More: How to Create Dependent Drop Down List in Google Sheets
Things to Remember
- Keep in mind, in the Data validation dialog box you need to use absolute reference in the independent dropdown and relative reference in the dependent dropdown.
- While naming Named ranges, you need to put a one-word name as it doesn’t take space. If you need to put more words use “_” as spaces.
Conclusion
Hopefully, the methods above will be enough for you to create multi row dynamic dependent dropdown list in Google Sheets. You can check the practice spreadsheet and then practice on your own. Please use the comment section below for any further queries or suggestions. You may also visit our OfficeWheel blog to explore more about Google Sheets.
Related Articles
- How to Insert a Drop-Down List in Google Sheets (2 Easy Ways)
- Create Drop Down List in Google Sheets from Another Sheet
- How to Remove Data Validation in Google Sheets
- Create Drop Down List for Multiple Selection in Google Sheets
- How to Create Conditional Drop Down List in Google Sheets
- How to Add Color to Drop Down List in Google Sheets (Easy Steps)
