Limiting filter criteria depending on cell value

[Beepee]

Hi All. I'm using Office 2019 Excel on Windows 11.
My worksheet relates to a gardening ‘almanac’. It lists:

1. Every commodity I might want to sow (flowers, herbs veg, etc)
2. All tasks related to that commodity and an action date
3. Usually in chronological order (But as an Excel Table so…)

The dataset is currently almost 700 lines.
I would like to set up a filtering option as shown (B2:H3) on the sample dataset, such that when a particular fltCommodity is selected then only the related Varieties show in the fltVariety drop-down.
EG: fltCommodity – Lettuce should limit fltVarieties to Arctic King/Lobjoits Green/Winter Density/Winter Marvel and so on.

On-Line I found, as an example of data-validation-based-on-another-cell:

Data Validation Based on Another Cell in Excel (4 Examples)

Here we'll see how Excel data validation based on another cell is created. Also, how different lists can be created by Excel Data Validation.

www.exceldemy.com

This example uses three Tables to select Ice-cream or Juice flavours. Would I have to set up an individual Table for each Commodity and list the relevant varieties or is there a way to glean the varieties from the main un-filtered table? I am NOT expecting anyone to do this for me – just point me in the right direction please.

Sorry -- tried to attach an image but received message saying Too Large!!??

 

[Beepee]

Beepee again attaching an XL2BB file (I hope!).

FilterTrial.xlsm
	B	C	D	E	F	G	H
2	Filtering Criteria Selection
3	FVH	Comodity	Variety	Key	Activity	Due Date	Action Date
4
5	The above two rows to be used as Criteria range for an Advanced Filter
6
7	---- Jobs for this set of filters ----
8	FVH	Comodity	Variety	Key	Activity	Due Date	Action Date
9	B	Blackcurrant	Misc	Cut	Take cuttings - Monty says OCT (up to end November)	02/10/2017	NR
10	B	Gooseberry	Misc	Cut	Take cuttings - Monty says OCT (up to end November)	02/10/2017	NR
11	B	Red Currant	Misc	Feed	with two good handfulls of blood,fish& bone	02/10/2017	NR
12	B	Red Currant	Misc	Cut	Take cuttings - Monty says OCT (up to end November) feed BFB	02/10/2017	NR
13	B	Rhubarb	Unknown	Sow	Plant new crowns up to end of February	02/10/2017	NR
14	S	Seeds	Misc	Misc	Order NOW from Premier Seeds 2021 no stock on Broad Beans.	07/10/2017	Later
15	V	Brassica	Misc	Misc	Remove butterfly net & Re-tie plants before winds pick-up	15/10/2017	13-Oct
16	F	Candytuft	Dwarf Fairyland	Sow	1st Candytuft 2021 - Cornflower in error	15/10/2017	NR
17	V	Lettuce	Arctic King	Sow	Oct wk 2	15/10/2017	25-Oct
18	V	Lettuce	Arctic King	T/P	Aug wk 4	15/10/2017	NS
19	V	Lettuce	Winter Marvel	Sow	Oct wk 2	15/10/2017	25-Oct
20	V	Lettuce	Winter Marvel	T/P	Aug wk 4	15/10/2017	NS
21	H	Rocket	Buzz	T/P	Sep Wk 3	15/10/2017	NS
22	H	Rosemary	Misc	Cut	Last chance to trim before spring!	15/10/2017	NR
23	F	Yellow Rattle	Rhinanthus	Sow	Not until November	15/10/2017	NR
24	V	Brussels Sprout	Brodie	Harvest	Begin harvest of earlies	23/10/2017	NA
25	S	Frost	Misc	Misc	First frost due in Hillesley!!!!!	25/10/2017	02-Dec
26	S	Greenhouse	Misc	Misc	Disinfect greenhouse and insulate	23/10/2017	NR
27	V	Leek	Misc	Harvest	Begin to harvest	23/10/2017	10-Nov
28	V	Parsnip	Misc	Harvest	Begin harvest (best after frost)	23/10/2017	28-Sep
29	B	Rhubarb	Misc	Misc	Tidy up existing plants	23/10/2017	24-Oct
30	V	Shallot	Misc	Misc	Buy sets from DP. (Premier 2021)	23/10/2017	OS
31	S	Wash Down	Misc	Misc	Disinfect pots etc.	23/10/2017	NR
32	V	Pea	Kelvedon Wonder	Sow	into 9cm pots Kelvedon Wonder (Monty) 42 x 9cm pots	31/10/2017	25-Oct
33	V	Runner Bean	Misc	Last	Cut down and clear.	31/10/2017	17-Oct
34						01/11/2017	Done
35	B	Blackberry	Unknown	Sow	Buy and plant (up to end February)	02/11/2017	NR
36	B	Blackcurrant	Misc	Cut	Take cuttings - Monty says OCT (up to end November)	02/11/2017	NR
37	B	Gooseberry	Misc	Cut	Take cuttings - Monty says OCT (up to end November)	02/11/2017	NR
38	B	Red Currant	Misc	Cut	Take cuttings - Monty says OCT (up to end November)	02/11/2017	NR
39	V	Asparagus	Misc	Cut	Cut down when foliage yellows	07/11/2017	NS
40	V	Cucumber	Misc	Last	Last Harvest	07/11/2017	25-Oct
41	V	Kale	Vates Blue Curled	T/P	Sep Week2 to garden Sown direct 22 Sep Need to thin!	07/11/2017	NS
42	S	Lime	Misc	Feed	Lime next season Brassica patch (after Legumes)-- raise Ph	07/11/2017	12-Nov
43	S	Manure	Misc	Feed	Next seasons Brassicas patch (after Legumes removed)	07/11/2017	Later
44	S	Manure	Misc	Feed	Rhubarb & raspberry, etc. plots	07/11/2017	28-Dec
45	S	Manure	Misc	Feed	Manure and rejuvinate LETTUCE patch	07/11/2017	AR
46	S	Manure	Misc	Feed	Manure Legume patch -- (when 'Roots' cleared)	07/11/2017	AR
47	S	Mole Deterent	Misc	Misc	Check batteries ('D' Cell)	07/11/2017	NR
48	S	Seeds	Misc	Misc	Order seeds (Kings/ Real Seed Co/Premier)	07/11/2017	24-Nov
49	F	Aconite	Winter Wolfs Bane	Sow	5th sowing into pots --Winter	15/11/2017	NS
50	V	Broad Bean	Aquadulce	T/P	1st tubes to garden	15/11/2017	NS
51	V	Brussels Sprout	Brodie	Harvest	Begin to harvest	15/11/2017	08-Nov
52	F	Cowslip	Common Wild	Sow	1st - cowslip and oxlip in pots	15/11/2017	NS
53	F	Cowslip	Great Oxlip	Sow	1st - cowslip and oxlip in pots	15/11/2017	NS
54	H	Garlic	Solent Wight Softneck	Sow	Plant into sandy soil & feed nitrogen (2021 = Elphant)	15/11/2017	NR
55	S	Manure	Misc	Feed	Potato patch	15/11/2017	NS
56	V	Shallot	Misc	Feed	Prepare bed and add Growmore. No manure	21/11/2017	Later
57	S	Cloche	Misc	Misc	Cover lettuce in severe weather	23/11/2017	AR
58	V	Lettuce	Winter Density	Sow	Nov wk 2	23/11/2017	NS
59	F	Yellow Rattle	Rhinanthus	Sow	Now is the best time	23/11/2017	NS
60	V	Broad Bean	Aquadulce	Sow	2nd onto wet tissue (50) Aqua/Sutton/Grano Violetto X 2	30/11/2017	26-Nov
61	F	Marigold	misc	Last	Clear all from garden and save any seed required	30/11/2017	29-Nov
62						01/12/2017	Done
63	B	Blackcurrant	Unknown	Sow	Buy and plant (up to end February)	02/12/2017	NR
64	B	Gooseberry	Unknown	Sow	Buy and plant (up to end February)	02/12/2017	NR
65	B	Raspberry	Misc	Sow	Buy and plant (up to end February)	02/12/2017	NR
66	B	Raspberry	Misc	Cut	Prune autumn fruiting (up to end February)	02/12/2017	NR
67	B	Raspberry	Unknown	Cut	Best time to dig suckers and t/p (any time possible)	02/12/2017	NR
68	B	Red Currant	Misc	Misc	Buy and plant (up to end March)	02/12/2017	NR
69	V	Shallot	Red Sun	Sow	Plant 3 rows of sets x 15 Cover with wire net	04/12/2017	Later
70	V	Brassica	Misc	Feed	Feed Nitrogen - clean up dying leaves (2021 Growmore)	07/12/2017	08-Dec
71	V	Lettuce	Winter Marvel	P/O	Nov wk 2	07/12/2017	NS
72	H	Rocket	Apollo	Sow	Buzz/Apollo 15cm pot in hothouse	07/12/2017	08-Dec
73	V	Broad Bean	Aquadulce	P/O	2nd sowing into t/r tubes	15/12/2017	02-Dec
74	V	Lettuce	Arctic King	P/O	Oct wk 2	15/12/2017	NS
75	V	Lettuce	Lobjoits Green	Sow	Dec Wk 2	15/12/2017	08-Dec
76	V	Lettuce	Arctic King	P/O	Sep wk 3	15/12/2017	06-Dec
77	V	Lettuce	Lobjoits Green	P/O	Sep wk 3	15/12/2017	06-Dec
78	V	Lettuce	Winter Density	P/O	Sep wk 3	15/12/2017	06-Dec
79	V	Lettuce	Winter Marvel	P/O	Sep wk 3	15/12/2017	06-Dec
80	V	Onion	Red Brunswick	Sow	few in g'hse (as per JH Dec'20 NL) Red Brunswick in cells!	23/12/2017	NS
81	V	Pea	Misc	Misc	Pinch out tips (as per Fairygirl GW Forum)	23/12/2017	06-Dec
FilteringTrial

Cell Formulas
Range	Formula
G9:G13	G9	=DATE(2017,10,2)
G14	G14	=DATE(2017,10,7)
G15:G23	G15	=DATE(2017,10,15)
G24,G26:G31	G24	=DATE(2017,10,23)
G25	G25	=CN_FirstFrost
G32:G33	G32	=DATE(2017,10,31)
G34	G34	=DATE(2017,11,1)
G35:G38	G35	=DATE(2017,11,2)
G39:G48	G39	=DATE(2017,11,7)
G49:G55	G49	=DATE(2017,11,15)
G56	G56	=DATE(2017,11,21)
G57:G59	G57	=DATE(2017,11,23)
G60:G61	G60	=DATE(2017,11,30)
G62	G62	=DATE(2017,12,1)
G63:G68	G63	=DATE(2017,12,2)
G69	G69	=DATE(2017,12,4)
G70:G72	G70	=DATE(2017,12,7)
G73:G79	G73	=DATE(2017,12,15)
G80:G81	G80	=DATE(2017,12,23)

Named Ranges
Name	Refers To	Cells
CN_FirstFrost	=FilteringTrial!$L$1	G25

Cells with Data Validation
Cell	Allow	Criteria
C4	List	=Sheet2!$B$3:$B$38
D4	List	=Sheet2!$D$3:$D$23