Data Validation Dependent List

Kasey21

New Member
Joined
Sep 21, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
1600706322819.png


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.

1600706828264.png
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,365
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Sep 21, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,365
Office Version
  1. 2016
Platform
  1. Windows
You could build a list for the B9 LoV and then use OFFSET to make it available:

Kasey21.xlsx
ABCD
1Procedure Follow Up Activity DeescriptionFREE TEXT HEADERWorkLoV
2Appeal/Dispute - ApprovedAPPEAL APPROVEDDENIED AUTHORIZATION
3Appeal/Dispute - AuthorizationDENIED AUTHORIZATIONMISSING AUTHORIZATION
4Appeal/Dispute - AuthorizationMISSING AUTHORIZATION 
5Test 1Test 1 stuff 
6Test 2Test 2 stuff1 
7Test 2Test 2 stuff2 
8Test 2Test 2 stuff3 
9 
Lists
Cell Formulas
RangeFormula
D2:D9D2=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
3Appeal/Dispute - Authorization
4
5
6
7
8
9DENIED AUTHORIZATION
Entry
Cells with Data Validation
CellAllowCriteria
B9List=OFFSET(Lists!$D$2,,,COUNTIF(Lists!$D$2:$D$99,"> "))
B3List=Lists!$A$2:$A$8
 

Watch MrExcel Video

Forum statistics

Threads
1,127,006
Messages
5,622,138
Members
415,880
Latest member
Bruce0203

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top