1
00:00:00,080 --> 00:00:04,301
A dependent dropdown should show items that match the chosen category.

2
00:00:04,301 --> 00:00:08,139
In our fictional workshop order, Stationery means Pen or Notebook;

3
00:00:08,139 --> 00:00:10,058
Catering means Coffee or Tea.

4
00:00:10,058 --> 00:00:14,279
We will use visible helper cells so the second dropdown reads

5
00:00:14,279 --> 00:00:17,733
a real range, and then check both category choices.

6
00:00:18,333 --> 00:00:22,055
Open a copy of the practice workbook in Google Sheets.

7
00:00:22,055 --> 00:00:25,405
Columns A and B hold four category-item rows.

8
00:00:25,405 --> 00:00:29,500
Cell F two is the selected category, and column H will

9
00:00:29,500 --> 00:00:30,989
be a helper list.

10
00:00:30,989 --> 00:00:34,339
Check these coordinates before entering a formula; mismatched columns

11
00:00:34,339 --> 00:00:36,200
make a dropdown appear empty.

12
00:00:36,800 --> 00:00:41,753
Select F two and make a dropdown with the two categories Stationery and Catering.

13
00:00:41,753 --> 00:00:42,815
Choose Stationery first.

14
00:00:42,815 --> 00:00:46,706
The category is the input that the helper formula will read;

15
00:00:46,706 --> 00:00:50,598
there is no need to hide this input or build a

16
00:00:50,598 --> 00:00:53,429
complex named range for the single-row example.

17
00:00:54,029 --> 00:00:56,906
In H two, enter the displayed FILTER formula.

18
00:00:56,906 --> 00:01:00,863
It returns item names from B two through B five only

19
00:01:00,863 --> 00:01:03,741
when the category in A matches F two.

20
00:01:03,741 --> 00:01:08,058
For Stationery, H two and H three should show Pen and Notebook.

21
00:01:08,058 --> 00:01:10,936
Check that result before creating the second dropdown.

22
00:01:11,536 --> 00:01:13,541
Select the Item cell, G two.

23
00:01:13,541 --> 00:01:17,217
Add a dropdown from a range and choose H two through

24
00:01:17,217 --> 00:01:18,887
H three as its source.

25
00:01:18,887 --> 00:01:22,563
The range picker expects cells, so enter the helper range there,

26
00:01:22,563 --> 00:01:24,234
not the FILTER formula text.

27
00:01:24,234 --> 00:01:27,910
Open the menu to check that Pen and Notebook are offered.

28
00:01:28,510 --> 00:01:31,056
Change F two from Stationery to Catering.

29
00:01:31,056 --> 00:01:35,056
The helper list becomes Coffee and Tea, and the Item menu

30
00:01:35,056 --> 00:01:36,511
offers those two names.

31
00:01:36,511 --> 00:01:40,511
In our fictional practice test, G two retained the old Pen

32
00:01:40,511 --> 00:01:42,329
value with a validation warning.

33
00:01:42,329 --> 00:01:46,330
Select Coffee or Tea to leave the row valid; the dropdown

34
00:01:46,330 --> 00:01:48,148
does not clear it automatically.

35
00:01:48,748 --> 00:01:52,534
The completed practice should present Pen and Notebook for Stationery,

36
00:01:52,534 --> 00:01:54,805
then Coffee and Tea for Catering.

37
00:01:54,805 --> 00:01:58,969
If the second list is blank or unchanged, check the F

38
00:01:58,969 --> 00:02:03,134
two reference, the FILTER result in H two and H three,

39
00:02:03,134 --> 00:02:05,405
and the G two validation range.

40
00:02:05,405 --> 00:02:07,298
Recheck any previously selected item.