How to create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row? (Cascade Drop Down Menu)

Status
Not open for further replies.

Lacan

Board Regular
Joined
Oct 5, 2016
Messages
85
Office Version
  1. 365
Platform
  1. Windows
Hello Guys,

Through your help and many other tutorial excel videos came this way that almost Im achieving the goal - create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row.

Have this massive extent List with 7 columns and more than 2300 rows I will give you the sample data very developed to ask for your expertise feedback.

Without Vba and without filter and sort function (because unfortunately in my professional lap top dont have this 2 functions in excel version).

1581635185042.png



The Goals:

1. Make data validation with unique values from every List in every Columns (in The Main Table) to make more easier and simple to choose for instead of duplicates ones like this:

1581635863035.png



2. Create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row:

a) When I choose in The Main Table:

299 Lx - Amadora (MÁgua - Sul) > HOSPITAL JB LONDON > Amadora > (Logical should only get 2 options "Fernando" or "Maria") instead get this:

1581636519834.png


ALL DATA:

Book1
BCDEFGHIJKLMNO
1DATA LISTTHE MAIN TABLE
2BrickCenterCityNameBrickCenterCityName
3299 Lx - Amadora (MÁgua - Sul)CENTRO CLINICO AVENIDAAmadoraAnna299 Lx - Amadora (MÁgua - Sul)HOSPITAL JB LONDONAmadoraDynom2HOSPITAL JB LONDON
4299 Lx - Amadora (MÁgua - Sul)CENTRO CLINICO AVENIDAAmadoraAnna Simon
5299 Lx - Amadora (MÁgua - Sul)CENTRO CLINICO AVENIDAAmadoraAladin
6299 Lx - Amadora (MÁgua - Sul)CENTRO CLINICO AVENIDAPortoSerge
7299 Lx - Amadora (MÁgua - Sul)HOSPITAL JB LONDONAmadoraFernando
8299 Lx - Amadora (MÁgua - Sul)CENTRO CLINICO AVENIDAAmadoraBlyde
9299 Lx - Amadora (MÁgua - Sul)HOSPITAL JACK BARCELONAPortoDynom
10299 Lx - Amadora (MÁgua - Sul)CENTRO CLINICO AVENIDAAmadoraVenom
11300 Lx - Amadora (MÁgua - Sul)CENTRO CLINICO FRANKAmadoraYalan
12300 Lx - Amadora (MÁgua - Sul)Clínica LAR MEDICOSamoucoAnna0
13300 Lx - Amadora (MÁgua - Sul)Clínica LAR MEDICOSamoucoCosta
14300 Lx - Amadora (MÁgua - Sul)Clínica LAR MEDICOSamoucoMaria
15300 Lx - Amadora (MÁgua - Sul)Clínica LAR MEDICOSamoucoCesar
16300 Lx - Amadora (MÁgua - Sul)Clínica LAR MEDICOSamoucoCarlos
17300 Lx - Amadora (MÁgua - Sul)HOSPITAL JB LONDONAmadoraMaria
18300 Lx - Amadora (MÁgua - Sul)Clínica LAR JONHAmadoraJesus
19301 Lx - Amadora (MÁgua - Sul)Clínica LAR JONHAmadoraMaria
20301 Lx - Amadora (MÁgua - Sul)Clínica LAR JONHAmadoraLuzia
21301 Lx - Amadora (MÁgua - Sul)Clínica MEDICA SALisboaGuilherme
22301 Lx - Amadora (MÁgua - Sul)Clínica MEDICA SALisboaRogerio
23301 Lx - Amadora (MÁgua - Sul)Clínica MEDICA SALisboaPedro
24301 Lx - Amadora (MÁgua - Sul)DR PETER SASamoucoAlbino
25302 LxCLINIC ABCLisboaNaxos
26
27
Esta (2)
Cell Formulas
RangeFormula
L3L3=SUM(COUNTIFS(C3:C25,I3, D3:D25,J3))
M3M3=INDEX(C3:E25, MATCH(I3,C3:C25,0), MATCH(J3,D3:D25,0))
H12H12=COUNTIF(INDIRECT(brick),H12)<2
Named Ranges
NameRefers ToCells
'Esta (2)'!_FilterDatabase='Esta (2)'!$B$2:$E$25L3:M3, H12
brick='Esta (2)'!$B$3:$B$25H12
'Esta (2)'!Lx_299='Esta (2)'!$C$3:$E$10L3:M3
Cells with Data Validation
CellAllowCriteria
I4List=INDIRETO(H20)
I5:I10,J4:K10List=INDIRETO(SUBST(H5;" ";""))
H3List=brick
I3List=DESLOCAMENTO($C$2; CORRESP(H$3;$B$3:$B$25;0);0;CONTAR.SE($B$3:$B$25;$H$3);1)
J3List=DESLOCAMENTO($D$2; CORRESP(I$3;$C$3:$C$25;0);0;CONTAR.SE($C$3:$C$25;$I$3);1)
K3List=DESLOCAMENTO($E$2; CORRESP(I$3;$C$3:$C$25;0);0;CONTAR.SE($D$3:$D$25;$J$3);1)




Can you help me guys?

Thank you very much.
 

Attachments

  • 1581635648985.png
    1581635648985.png
    24.5 KB · Views: 0

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,360
Office Version
  1. 2016
Platform
  1. Windows
Hi Lacan,

I believe I have done what you ask... but you may need to take coffee breaks between dropdown selections as it takes 5 seconds to respond on my PC.

Lacan.xlsx
ABCDEFGHIJKLMNO
1$L$4:$L$6$M$4:$M$5$N$4:$N$5$O$4:$O$5
2AreaCentreCityNameAreaCentreCityName$G$5$H$5$I$5
3299 GothamAdams ClinicGothamJohn302 XanaduHolby GeneralGothamMichaelAreaCentreCityName
4299 GothamAdams ClinicGothamSarah301 SherwoodFaith MedicalRomeGillian299 GothamAdams ClinicGothamMargaret
5299 GothamAdams ClinicGothamSimon299 GothamAdams ClinicParisJames301 SherwoodFaith MedicalParisJames
6299 GothamAdams ClinicParisMargaret302 Xanadu   
7299 GothamAdams ClinicParisJames    
8299 GothamFaith MedicalParisPeter    
9299 GothamFaith MedicalGothamVanessa    
10301 SherwoodFaith MedicalGothamSusan    
11301 SherwoodFaith MedicalRomeGillian    
12301 SherwoodHolby GeneralRomeAlex    
13301 SherwoodHolby GeneralRomeJames    
14302 XanaduHolby GeneralParisGary    
15302 XanaduHolby GeneralGothamMichael    
16302 XanaduClinic OssoParisThomas    
17302 XanaduClinic OssoMilanThomas    
18    
Sheet1
Cell Formulas
RangeFormula
L1:O1L1=CELL("address",L4)&":"&ADDRESS(COUNTIF(L$4:L$1000,"> ")+ROW(L$3),COLUMN())
M2:O2M2=ADDRESS(COUNTIF(G$3:G$10,"> ")+ROW(G$2),COLUMN(G$2))
L4:L18L4=INDEX(B$3:B$2500,MATCH(0,INDEX(COUNTIF(L$3:L3,B$3:B$2500),),0))&""
M4:M18M4=IFERROR(INDEX(C$3:C$2500,AGGREGATE(15,6,ROW(C$3:C$2500)-ROW($C$2)/((B$3:B$2500=INDIRECT(M$2))*(COUNTIF(M$3:M3,C$3:C$2500)=0)),1)),"")
N4:N18N4=IFERROR(INDEX(D$3:D$2500,AGGREGATE(15,6,ROW(D$3:D$2500)-ROW($C$2)/((C$3:C$2500=INDIRECT(N$2))*(B$3:B$2500=INDIRECT(M$2))*(COUNTIF(N$3:N3,D$3:D$2500)=0)),1)),"")
O4:O18O4=IFERROR(INDEX(E$3:E$2500,AGGREGATE(15,6,ROW(E$3:E$2500)-ROW($C$2)/((D$3:D$2500=INDIRECT(O$2))*(C$3:C$2500=INDIRECT(N$2))*(B$3:B$2500=INDIRECT(M$2))*(COUNTIF(O$3:O3,E$3:E$2500)=0)),1)),"")
Cells with Data Validation
CellAllowCriteria
G3:J12List=INDIRECT(L$1)
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,126,904
Messages
5,621,571
Members
415,847
Latest member
nigeywigey

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