Try the example, then check the result.
Download the fictional material and follow the steps below. The video uses English illustrative diagrams; it does not present a live app recording. Product controls may vary by version.
Check the answer before applying the method to your own data. How we review guides ↗
Watch the complete lesson.
The video uses English illustrative diagrams; it does not present a live app recording. English captions. Product menus may differ in your account.
Quick answer. Create a Category dropdown, generate matching Item choices in helper cells with FILTER, then use Dropdown (from a range) for the Item cell. Point the range picker to helper cells, not to a formula string.
Use the fictional practice file and independent answer check. Work in a permitted copy.
Before you start. Use the fictional four-row mapping. Stationery maps to Pen and Notebook; Catering maps to Coffee and Tea. This is a single-row demo, not a multi-row automatic form.
1. Lay out the mapping
Open a copy of the practice workbook in Google Sheets. Columns A and B hold four category-item rows. Cell F two is the selected category, and column H will be a helper list. Check these coordinates before entering a formula; mismatched columns make a dropdown appear empty.
Checkpoint: Four fictional mapping rows.
2. Create the first dropdown
Select F two and make a dropdown with the two categories Stationery and Catering. Choose Stationery first. The category is the input that the helper formula will read; there is no need to hide this input or build a complex named range for the single-row example.
Checkpoint: F2 contains a category.
3. Generate matching helper items
In H two, enter the displayed FILTER formula. It returns item names from B two through B five only when the category in A matches F two. For Stationery, H two and H three should show Pen and Notebook. Check that result before creating the second dropdown.
=FILTER(B2:B5,A2:A5=F2)The formula goes in H2; its two results fill H2:H3 for this four-row example.
Checkpoint: Helper cells contain matching items.
4. Point the Item dropdown to cells
Select the Item cell, G two. Add a dropdown from a range and choose H two through H three as its source. The range picker expects cells, so enter the helper range there, not the FILTER formula text. Open the menu to check that Pen and Notebook are offered.
Checkpoint: Validation reads the helper cells.
5. Switch category and inspect choices
Change F two from Stationery to Catering. The helper list becomes Coffee and Tea, and the Item menu offers those two names. In our fictional practice test, G two retained the old Pen value with a validation warning. Select Coffee or Tea to leave the row valid; the dropdown does not clear it automatically.
Checkpoint: Both categories checked.
Verify the result
With Category = Stationery, Item choices are Pen and Notebook. With Category = Catering, Item choices are Coffee and Tea. After switching categories, check any old selected item and reselect a valid one if it remains.
If it still does not work
A formula typed directly into a validation range picker may be rejected; point it at helper cells. If choices do not update, check the category reference, helper formula range, and whether the validation rule points to the helper output. Do not promise automatic clearing of a prior selection.
Official sources
- https://support.google.com/docs/answer/186103?hl=en
- https://support.google.com/docs/answer/3093197?hl=en
Any video panel without a marked actual-app capture is an illustrative diagram of the fictional exercise.
Practice downloads.
The exercise files contain invented information. Save a local copy before editing.