Hi! 🙂 Glad to announce a way of creating Dependent Drop-Down Lists → DDLs in Google Sheets. No Script Needed! Show
Boom: COPY-SAMPLE-FILE-LINK Please take a look at sheet \Data/. The lists are dependent:
Another way shown on sheet \Work/:
Bonus #2: use any datatype: text, number, boolean, dates. Bonus #3: No-script = no limit on 500 values per list. Hope you’ve improved your Google Sheets skills during the quarantine. Cheers!
Learning how to create a dependent drop-down list in Google Sheets is useful to create several lists of options for users to select from. It is no surprise that most of us are well versed in creating dropdowns using the data validation feature in Google Sheets. If you are not familiar with the data validation feature, do not be shy to check out our tutorial on how to use it, and learn through real-life examples for better understanding! 😇 Let’s take an example:Imagine you are a supplier for chairs. Your main income derives from selling office chairs and restaurant seatings. Your catalog contains hundred types of chairs from many different brands. It is always a hassle when taking orders from companies in bulk as there would be errors in the data collected. By using the dependent drop-down list in Google Sheets, we are able to minimize the errors and collect cleaner and more reliable data. As shown above, using the dependent drop-down list we are able to let the users select which type of chairs and what brands to order. Let us use a real-life example as a guide to learn how to create a multi-row-dependent drop-down list in Google Sheets. You may make a copy of the spreadsheet using the link I have attached below. A Real Example of Using Dependent Drop Down List FeatureIn this example, you are an Italian catering company that supplies food for different events. For customers like event management companies, they have several events lined up that would need you to cater to. This makes it easy to make errors in the order form collected from your customers. Let us create a multi-row dependent dropdown list in Google Sheets containing all the dishes from the menu for customers to select from. This enables the customers to dependently select the dishes according to the events and the number of courses they want in an event. Once the dependent dropdown list is created, we will be able to send this list through email to customers for them to list down all the dishes they want for each event. How to Use the Dependent Drop Down List Feature in Google SheetsCreating a multi-row dependent dropdown list in Google Sheets requires:
The tedious part that needs more attention is when we are preparing the Prep Data List. It requires us to utilize several functions in a single formula. Before you start, it is preferred to create three different sheet tabs for the Dependent Dropdown List, Master List, and Prep Data List. This will enable your Google Sheets to be cleaner and easily navigated. Second, you will also need to name the different ranges of data. This enables formulas to be easily understood and read. For example, data within the cells of B5:B9 are named as Appetizers. To do this, select Data, then Named ranges. In the pop-up, input the desired name for the range of cells and select the range of cells to be named. Take note that names inputted can only be a single word. If you want to put multiple words, input an underscore in between the words instead of pressing the spacebar. Remember to categorize the remaining courses as well! (a) First Dropdown List
(b) Prep Data ListThe reason for preparing a separate data list is because the data validation feature does not allow us to enter formulas directly. As the Second Dependent Dropdown List is reactive to the First Dropdown List, we will prepare a separate data list using several functions in Google Sheets to help us link them together. Follow the steps slowly, and I will explain why we use each function for your better understanding at the end.
Let me explain how this formula works. Here is a visual representation of the entire formula: First, the TRANSPOSE function is to help swap an array of data from columns to rows. Second, the INDEX and MATCH functions help us to look up data from the Dependent Drop Down tab to the Master List. The first argument in the INDEX function is to locate all the data located in cell B5:B9 in the Master List. The second argument in the INDEX function is to specify which rows to return. Since we wanted all the rows to return, we left it blank. In the third argument, we used the MATCH function to match the course selected in the Dependent Drop-Down tab to the range of courses in the Master List tab. We input ‘0’ as the match_type as we want the exact match to return. Hence, if in the Dependent Drop-Down tab we select Soup for the dropdown in B5, the formula in the Data Prep Tab will return the list of dishes for Soup. (c) Second Dependent Dropdown ListFinally, we can create a data validation for creating the dependent dropdown list.
Once you fill in the formulas for all the other cells, your sheet will look something like this. There you go! You have successfully created a dependent dropdown list! If you are still unclear with any of the functions used within this tutorial, you can also check out our tutorials on how to use the TRANSPOSE, INDEX and MATCH functions for a better understanding! |