Cascade Dropdown Values

[psycoperl]

I would like to have drop down options to ensure that only certain values are able to be selected in certain columns, where a value in on column filters options that appear for the next column.
While I know of using the List option in Data Validation, I have only ever used it for a static list and am having issues figuring how to filter options and also to only show unique values.

Working off of three sheets:
- Claims - This is my main sheet where I am trying to enter tracking data.
- InsurancePlanList - This is where a list of insurance plans types, and policy numbers
- ProviderListing - sheet that contains three columns - "Type" (Column A), "Facility" (Column B) and "Provider" (Column C)

In my Claims sheet (Rows 1 and 2 sheet heading info and Row 3 is the Column Headings)

In column C: "Claim Type" - I would like to have a drop-down list values are the unique list of "Type" (Column C) from the InsurancePlanList using a data validation list with the criteria =UNIQUE(InsurancePlanList!C2:C300,FALSE,TRUE) however I get an error that says "The Source currently evaluates to an error" where the lists do not show any values.

In Column D: "Insurance Plan" - I would like to have a drop-down list values of "PlanName" (Column A) from InsurancePlanList where the "Type" (Column C) matches the value selected in "Claim Type" (Claims!$C)

In Column F: "Facility" - I would like to have the drop-down list of values from ProviderListing!$B ("Facility") where ProviderListing!$A ("Type") matches the value selected in "Claim Type" (Claims!$C)

In Column G: "Provider" - I would like to have the drop-down list of values be from ProviderListing!$C ("Provider") where ProviderListing!$A ("Type") matches the value selected in "Claim Type" (Claims!$C) AND ProviderListing!$B ("Faciltiy") matches the value selected in "Facility" (Claims!$F)

Any suggestions would be greatly appreciated. I would like to ensure that any solution works on Windows, Mac, Web and iPadOS Excel App if possible.

Claims Tracking.xlsx
	A	B	C	D	E	F	G
3	Record#	Claim Date	Claim Type	Insurance Plan	Policy Number	Facility	Provider
4	001	2023-01-03
5	002	2023-01-04
6	003	2023-01-11
Claims

Cell Formulas
Range	Formula
A4:A63	A4	=SEQUENCE(COUNT($B4:$B300),1,1,1)
E4:E6	E4	=IF(XLOOKUP(D4,InsurancePlanList!A:A,InsurancePlanList!B:B,"",0,1)=0,"",XLOOKUP(D4,InsurancePlanList!A:A,InsurancePlanList!B:B,"",0,1))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C4:C230	List	=UNIQUE(InsurancePlanList!C2:C300,FALSE,TRUE)
D4:D230	List	=InsurancePlanList!$A$2:$A$32

Claims Tracking.xlsx
	A	B	C
1	PlanName	Policy Number	Type
2	PLAN 1	A1111	Dental
3	Plan2	A2222	Hospital
4	PLAN3	A3333	Medical
5	PLAN4	A4444	Pharmacy
6	PLAN5	A5555	Pharmacy
7	PLAN6	A6666	Pharmacy
8	PLAN7	A7777	Vision
9	PLAN8	A8888	Z_OTC
10	PLAN9	A9999	Z_OTC
11	PLAN10	A11110	Z_OTC
12	PLAN11	A12221	Z_OTC
13
InsurancePlanList

Claims Tracking.xlsx
	A	B	C
1	Type	Facility	Provider
2	Medical	01 Facilty	John Provider
3	Medical	02 Facility	Mary Provider
4	Pharmacy	CVS	CVS 12345 - Main Street
5	Pharmacy	RiteAid	Rite Aid 104654 - Knolls
6	Hospital	City Hospital	City Hospital
ProviderListing

 

[Eric W]

I haven't looked over your entire post, but I can see one issue. Data Validation does not like some of the new array functions. In particular, the UNIQUE function in the C4 cell won't work as entered. To do it, pick an empty cell somewhere, say Z1. Enter your UNIQUE function in Z1. Then in the Data Validation, put =Z1# as the source and that should work.

 

[psycoperl]

I haven't looked over your entire post, but I can see one issue. Data Validation does not like some of the new array functions. In particular, the UNIQUE function in the C4 cell won't work as entered. To do it, pick an empty cell somewhere, say Z1. Enter your UNIQUE function in Z1. Then in the Data Validation, put =Z1# as the source and that should work.

That solved Column C - Thank you. (=UniqueLists!$A$1#)
Now the next fun step is how to filter the dropdown lists in the other column.

In Column D: "Insurance Plan" - I would like to have a drop-down list values of "PlanName" (Column A) from InsurancePlanList where the "Type" (Column C) matches the value selected in "Claim Type" (Claims!$C)

 

[psycoperl]

That solved Column C - Thank you. (=UniqueLists!$A$1#)
Now the next fun step is how to filter the dropdown lists in the other column.

In Column D: "Insurance Plan" - I would like to have a drop-down list values of "PlanName" (Column A) from InsurancePlanList where the "Type" (Column C) matches the value selected in "Claim Type" (Claims!$C)

I was able to adapt your suggestion to create the dropdown lists for the other columns. Thank you

Claims Tracking.xlsx
	A	B	C	D	E	F	G
3	Record#	Claim Date	Claim Type	Insurance Plan	Policy Number	Facility	Provider
4	001	2023-01-03
Claims

Cell Formulas
Range	Formula
A4	A4	=IF(NOT(ISBLANK([@[Claim Date]])),COUNT(B$4:B4),"")

Cells with Data Validation
Cell	Allow	Criteria
C4:C233	List	=UniqueLists!$A$2#
D4:D16	List	=UniqueLists!$C$2#
F4	List	=UniqueLists!$E$2#
G4	List	=UniqueLists!$G$2#

Claims Tracking.xlsx
	A	B	C	D	E	F	G
1	Plan Type		Insurance Plans		Facility List		PROVIDER LIST
2
3	Dental
UniqueLists

Cell Formulas
Range	Formula
A2:A8	A2	=SORT(IF(UNIQUE(InsurancePlanList!$C$2:$C$300,FALSE,FALSE)=0,"",UNIQUE(InsurancePlanList!$C$2:$C$300,FALSE,FALSE)))
C2	C2	=SORT(UNIQUE(IF(FILTER(Insurance_Plans[PlanName],Insurance_Plans[Type]=Claims!C4,"")=0,"",FILTER(Insurance_Plans[PlanName],Insurance_Plans[Type]=Claims!C4,"")),FALSE,FALSE))
E2	E2	=SORT(UNIQUE(IF(FILTER(Provider_and_Facility[Facility],Provider_and_Facility[Type]=Claims!C4,"")=0,"",FILTER(Provider_and_Facility[Facility],Provider_and_Facility[Type]=Claims!C4,"")),FALSE,FALSE))
G2	G2	=SORT(UNIQUE(IF(FILTER(Provider_and_Facility[Provider],(Provider_and_Facility[Type]=Claims!C4)*(Provider_and_Facility[Facility]=Claims!F4),"")=0,"",FILTER(Provider_and_Facility[Provider],(Provider_and_Facility[Type]=Claims!C4)*(Provider_and_Facility[Facility]=Claims!F4),"")),FALSE,FALSE))
Dynamic array formulas.

 

[Eric W]

Happy to help!