If you have ever built a tracker or a form in Google Sheets, you know that standard drop-down lists are great for data integrity. However, sometimes you need the options in one list to change based on what was selected in a previous list—this is known as a Dependent Drop-down List. For example, if you select 'Fruits' in Column A, Column B should only show 'Apple, Banana, Orange'.

In this guide, we will walk through the most efficient method to create these dynamic lists using Named Ranges and the INDIRECT function.

Step 1: Prepare Your Source Data

The first step is to organize your data into columns. Create a separate sheet (you can name it 'Data') to house your categories and their sub-items.

• In row 1, list your Main Categories (e.g., Electronics, Clothing, Furniture).
• Under each header, list the specific Sub-items belonging to that category.

Note: Ensure your Main Category headers do not contain spaces if you want the simplest setup. If they do have spaces, use underscores (e.g., Home_Office).

Step 2: Create Named Ranges

For Google Sheets to understand the relationship between the lists, you must define Named Ranges.

• Highlight the sub-items under your first category.
• Go to Data > Named ranges.
• In the panel that appears, name the range exactly the same as your Main Category header (e.g., if the header is 'Electronics', name the range 'Electronics').
• Repeat this for every category you have.

Step 3: Create the Primary Drop-down List

Now, go to the sheet where you want the user to enter data.

• Select the cell where you want the first (Main) category to appear.
• Go to Insert > Dropdown (or Data > Data Validation).
• In the settings, choose 'Dropdown (from a range)' and select the cells containing your Main Category headers from Step 1.
• Click Done.

Step 4: Use the INDIRECT Function for the Dependent List

This is the secret sauce. We need a helper area to pull the specific sub-items based on the user's selection.

• In an empty area of your sheet (or a hidden column), enter this formula: =INDIRECT(A2) (assuming A2 is where your primary dropdown is).
• This formula looks at the text in A2 and finds the Named Range with that exact name, displaying its contents in a list.

Step 5: Apply Data Validation to the Dependent Cell

Finally, we link the second dropdown to the results of our INDIRECT formula.

• Select the cell where you want the Sub-item dropdown to appear.
• Go to Insert > Dropdown.
• Select 'Dropdown (from a range)'.
• For the range, select the cells where your INDIRECT formula is outputting the list.
• Click Done.

Pro Tip: Handling Multiple Rows

If you are creating a table with many rows, the INDIRECT method described above needs a slight adjustment for each row. You would typically create a Helper Sheet where each row uses INDIRECT to pull the relevant named range for its corresponding row in the main tracker. This ensures that Row 5's dropdown doesn't accidentally show Row 2's options!

💡 Pro Tip: Keep your software updated to avoid these issues in the future.