Data Validation Dependent List

[Kasey21]

Hello,

First post here so I hope I am doing this correctly. I have a data validation list in cells B3 (Follow Up Activity Description) and B9 (Comment Header). There are 76 options available for B3. Cell B9 is dependent on what is chosen in B3. Each of the 76 options in B3 have 1-4 sub options which will be selected in B9 but I only want the options that appear in B9 to be those that are directly correlated to the choice made in cell B3. I hope this makes sense but is this at all possible. The picture below is a portion of the data validation criteria.

 

[Toadstool]

Hi Kasey21,

So I guess the B3 dropdown is using the table "Procedure Follow Up Activity Description".
Is B9 using the "FREE TEXT HEADER" table and if so what indicator decides which rows relate to a specific B3 selection?

 

[Kasey21]

Hi Toadstool,

You are correct. B3 dropdown is using the "Procedure Follow Up Activity Description" while B9 is using "Free Text Header". They are currently aligned by row as you see in "Procedure Follow Up Activity Description" there may be the same option repeated more than once. IE "Appeal/Dispute - Authorization". What I am wanting is when "Appeal/Dispute - Approved" is selected in B3 then my option(s) in B9 is "APPEAL APPROVED", when "Appeal/Dispute - Authorization" is selected in B3 then my option(s) in B9 are "DENIED AUTHORIZATION" or "MISSING AUTHORIZATION", and continues with the rest of the criteria. I just don't really know where to even start and I am a self taught novice when it comes to excel.

Hi Kasey21,

So I guess the B3 dropdown is using the table "Procedure Follow Up Activity Description".
Is B9 using the "FREE TEXT HEADER" table and if so what indicator decides which rows relate to a specific B3 selection?

 

[Toadstool]

You could build a list for the B9 LoV and then use OFFSET to make it available:

Kasey21.xlsx
	A	B	C	D
1	Procedure Follow Up Activity Deescription	FREE TEXT HEADER		WorkLoV
2	Appeal/Dispute - Approved	APPEAL APPROVED		DENIED AUTHORIZATION
3	Appeal/Dispute - Authorization	DENIED AUTHORIZATION		MISSING AUTHORIZATION
4	Appeal/Dispute - Authorization	MISSING AUTHORIZATION
5	Test 1	Test 1 stuff
6	Test 2	Test 2 stuff1
7	Test 2	Test 2 stuff2
8	Test 2	Test 2 stuff3
9
Lists

Cell Formulas
Range	Formula
D2:D9	D2	=IFERROR(INDEX(Table2[FREE TEXT HEADER],AGGREGATE(15,6,ROW(Table2[FREE TEXT HEADER])-1/(Table1[Procedure Follow Up Activity Deescription]=Entry!$B$3),ROW()-ROW($D$1))),"")

Kasey21.xlsx
	B
3	Appeal/Dispute - Authorization
4
5
6
7
8
9	DENIED AUTHORIZATION
Entry

Cells with Data Validation
Cell	Allow	Criteria
B9	List	=OFFSET(Lists!$D$2,,,COUNTIF(Lists!$D$2:$D$99,"> "))
B3	List	=Lists!$A$2:$A$8