Create Dependent Drop-Down List in Google Sheets with Data Validation

Table of Contents

1. Introduction
2. Creating a Drop-Down List for Customer Type
3. Creating a Drop-Down List for Product Category
4. Creating Range Names for Product Categories
5. Displaying Products Based on Selected Category
6. Transposing the Products Horizontally
7. Applying Data Validation to the Product Column
8. Adding New Products to the Drop-Down List
9. Removing Drop-Down Option from Headings
10. Turning the Sheet into a Master Sheet
11. Conclusion

Introduction

In this tutorial, we will learn how to create a dependent drop-down list in Google Sheets without having to write any code. This can be useful for anyone who needs to enter data into Google Sheets and wants to select options from a drop-down list based on the previous selection. We will go through step-by-step instructions on how to create the drop-down lists for customer type and product category, how to display the relevant products based on the selected category, and how to add new products to the drop-down list. Let's get started!

📝 Creating a Drop-Down List for Customer Type

To create a drop-down list for the customer type column, follow these steps:

1. Select the entire column for the customer type.
2. Go to the "Data" tab and click on "Data validation".
3. In the data validation window, leave the "Criteria" option as "List from a range".
4. Click on "Select data range" and choose the sheet where your customer list exists.
5. Select the customer list and click on "OK".
6. To add a rejection inverter or a data validation help text, select the respective options.
7. Click on "Save".

Now, you will see a drop-down option in every cell of the customer type column, allowing you to conveniently select the customer type.

📝 Creating a Drop-Down List for Product Category

Creating a drop-down list for the product category column follows similar steps:

1. Select the headings for the product category column.
2. Go to the "Data" tab and click on "Data validation".
3. In the data validation window, leave the "Criteria" option as "List from a range".
4. Click on "Select data range" and choose the headings for the product categories.
5. If there are no other data in the row, you can select the entire row instead.
6. Click on "OK" and then click on "Save".

Now, the drop-down list for the product category column is ready, and you can conveniently select the desired category.

📝 Creating Range Names for Product Categories

To display the products based on the selected category, we need to create range names for each product category. Follow these steps:

1. Select only the products related to a specific category.
2. Go to the "Data" tab and select "Named ranges".
3. Assign a name (e.g., "Cloth") to the selected range of products.
4. Repeat the process for other ranges, such as "Office Supplies".
5. Now you can see the range names in the sidebar.

These range names will be used later to show the relevant products based on the selected category.

📝 Displaying Products Based on Selected Category

The next step is to use the INDIRECT function to display the products that come under the selected category. Follow these steps:

1. Create a new sheet and apply the INDIRECT function on it.
2. Select the cell where your product category range name will appear (e.g., B2 in the product category column).
3. The INDIRECT function will automatically retrieve all the products belonging to the selected range name (e.g., "Office Supplies").
4. By default, the products will be displayed vertically.
5. To show the products horizontally, use the TRANSPOSE function.
6. Drag the formula down to cover the relevant cells under the product column.

Now, you will see the products that come under the selected category. Let's move on to the next step.

📝 Applying Data Validation to the Product Column

To apply data validation to the product column, follow these steps:

1. Select the relevant cell under the product column.
2. Go to the "Data" tab and click on "Data validation".
3. Click on "Select data range" and choose either a specific range or the entire row where you applied the INDIRECT function.
4. Adjust the other options in the data validation window according to your needs.
5. Click on "Save".

Now, the drop-down list in the product column will show all the products that come under the selected category.

📝 Adding New Products to the Drop-Down List

If you need to add a new product to the data, follow these steps:

1. Go to the relevant product category.
2. Insert a new cell in the range and add the new product.
3. The new product will automatically appear in the drop-down list.
4. If the new product does not show up, insert a cell in the range and drag the data to include the new product.

Now, you can select the new product from the drop-down list. You can also remove the drop-down option from the headings if desired.

📝 Turning the Sheet into a Master Sheet

Once the sheet is filled with data and formulas, it can be turned into a master sheet. Follow these steps:

1. Delete all the existing data to create a blank master sheet.
2. Start writing the new data according to your needs.

Turning the sheet into a master sheet allows for efficient management of dependent or multi-layered data. You can also copy the data to another sheet for further manipulation.

Conclusion

In this tutorial, we have learned how to create a dependent drop-down list in Google Sheets without writing any code. We have covered the step-by-step process for creating drop-down lists for customer type and product category, displaying relevant products based on the selected category, adding new products to the drop-down list, and turning the sheet into a master sheet. By following these instructions, you can efficiently manage and manipulate your data in Google Sheets. Happy data organizing!

Resources

• Google Sheets