Dependent Dropdown based on previous cell selection

S

sanket_sk

Board Regular
Joined
Dec 27, 2016
Messages
140
Office Version
  1. 365
Platform
  1. Windows
Dear All,



I have created a dependent dropdown list using filter and unique functions, it seems I have developed a static formula where the dropdown table is referring row number two values irrespective of row I am in

I want to develop a dependent dropdown, excel should show a dropdown list based on the previous cell value ( related reference) instead of a formula looking at only one cell (example D2).

Refer to my sheet here filtration happening based on cell numbers D2 & E2, I want to make it dynamic, when I am updating row number 3 filtration should happen based on cells available in row 3 and so on.

Basically, I want to use this functionality in the office 365 only sheet and hence I don't want to use any VBA, requesting you to help if you can suggest a better solution for dependent data validation.
S. NoDate of VisitRegion Town Party NaeName of PartnerName of EmployeeIssue If Any (Yes/No)Reason for Non Satisfaction Remark Action need to be Taken Owner Date of closure
13-Jan-22NorthDelhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)ASC2CSE1NoPartner03-Jan-22
23-Jan-22Noth Uttar Pradesh & UttarakhandTown4AAKRITI LIGHTING CO. ( INDIA)ASC2CSE1NoPartner03-Jan-22
33-Jan-22EastBihar, Odisha & JharkhandTown4SHIV ELECTRIC EMPO.ASC3CSE2NoPartner03-Jan-22
Drop Down Prep
BranchTownNameRegion
Bihar, Odisha & JharkhandTown1AAKRITI LIGHTING CO. ( INDIA)
Delhi & NCRTown2
Greater PunjabTown3
GujaratTown4
Uttar Pradesh & Uttarakhand
Products
BranchTownNameASCRegionCSE
Bihar, Odisha & JharkhandTown3VATIKAASC2EastCSE1
Bihar, Odisha & JharkhandTown3VICKY ELECTRICASC3EastCSE2
Delhi & NCRTown43-STRIPSASC4NorthCSE1
Delhi & NCRTown1A.A.ENTERPRISESASC2NorthCSE2
Delhi & NCRTown3A.R.ELECTRICALSASC4NorthCSE1
Delhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)ASC2NorthCSE1
Greater PunjabTown1BHARAT CROCKERY - 18ASC3NorthCSE1
Greater PunjabTown4BHAT ELECTRONICSASC3NorthCSE1
Greater PunjabTown4BINDRA RADIOSASC2NorthCSE3
GujaratTown2PRAKASH ELECTRONICSASC3WestCSE2
GujaratTown3PRANAV ELE.ASC4WestCSE1
GujaratTown3PRAVIN METAL CORPORATION,BARODAASC4WestCSE4
GujaratTown1PUROHIT APPLIANCEASC3WestCSE2
Uttar Pradesh & UttarakhandTown2INDIA LIGHT HOUSEASC2NorthCSE3
Uttar Pradesh & UttarakhandTown1SHIV ELECTRIC EMPO.ASC3NorthCSE3
Uttar Pradesh & UttarakhandTown2SINGHAL FURNITURESASC2NorthCSE2
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
i believe this is what you are looking for
--------------------
Book1
ABCDEFGHIJKLMN
1S. NoDate of VisitRegionBranchTown Party NameName of PartnerName of EmployeeIssue If Any (Yes/No)Reason for Non Satisfaction Remark Action need to be Taken Owner Date of closure
2144564NorthDelhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)ASC2CSE1NoPartner44564
3244564Noth Uttar Pradesh & UttarakhandTown4AAKRITI LIGHTING CO. ( INDIA)ASC2CSE1NoPartner44564
4344564EastBihar, Odisha & JharkhandTown4SHIV ELECTRIC EMPO.ASC3CSE2NoPartner44564
5444565Greater Punjab1BHARAT CROCKERY - 18
6
7
8
9
10
11Drop Down Prep
12
13BranchTownNameRegion
14Bihar, Odisha & JharkhandBiharTown1AAKRITI LIGHTING CO. ( INDIA)
15Delhi & NCRDelhiTown2
16Greater PunjabPunjabTown3
17GujaratGujaratTown4
18Uttar Pradesh & UttarakhandUttar
19
20Products
21
22BranchTownNameASCRegionCSE
23Bihar, Odisha & JharkhandTown3VATIKAASC2EastCSE1
24Bihar, Odisha & JharkhandTown3VICKY ELECTRICASC3EastCSE2
25Delhi & NCRTown43-STRIPSASC4NorthCSE1
26Delhi & NCRTown1A.A.ENTERPRISESASC2NorthCSE2
27Delhi & NCRTown3A.R.ELECTRICALSASC4NorthCSE1
28Delhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)ASC2NorthCSE1
29Greater PunjabTown1BHARAT CROCKERY - 18ASC3NorthCSE1
30Greater PunjabTown4BHAT ELECTRONICSASC3NorthCSE1
31Greater PunjabTown4BINDRA RADIOSASC2NorthCSE3
32GujaratTown2PRAKASH ELECTRONICSASC3WestCSE2
33GujaratTown3PRANAV ELE.ASC4WestCSE1
34GujaratTown3PRAVIN METAL CORPORATION,BARODAASC4WestCSE4
35GujaratTown1PUROHIT APPLIANCEASC3WestCSE2
36Uttar Pradesh & UttarakhandTown2INDIA LIGHT HOUSEASC2NorthCSE3
37Uttar Pradesh & UttarakhandTown1SHIV ELECTRIC EMPO.ASC3NorthCSE3
38Uttar Pradesh & UttarakhandTown2SINGHAL FURNITURESASC2NorthCSE2
main
Cells with Data Validation
CellAllowCriteria
D2:D5List=Branch
E5List=INDIRECT(HLOOKUP(D5,'branch tables'!$A$1:$E$2,2,0))
F5List=INDIRECT(SUBSTITUTE(VLOOKUP(D5,B14:C18,2)&E5," ",""))

---------------------
Book1
ABCDE
1Bihar, Odisha & JharkhandDelhi & NCRGreater PunjabGujaratUttar Pradesh & Uttarakhand
2BiharDelhiPunjabGujaratUttar
3
4BiharDelhiPunjabGujaratUttar
531111
62422
733
84
9
10Bihar3Delhi1Punjab1Gujarat1Uttar1
11VATIKAA.A.ENTERPRISESBHARAT CROCKERY - 18PUROHIT APPLIANCESHIV ELECTRIC EMPO.
12VICKY ELECTRIC
13
14
15
16Delhi2Punjab4Gujarat2Uttar2
17AAKRITI LIGHTING CO. ( INDIA)BHAT ELECTRONICSPRAKASH ELECTRONICSINDIA LIGHT HOUSE
18BINDRA RADIOSSINGHAL FURNITURES
19
20Delhi3Gujarat3
21A.R.ELECTRICALSPRANAV ELE.
22PRAVIN METAL CORPORATION,BARODA
23Delhi4
243-STRIPS
branch tables
 
Upvote 0
ExceLoki said:
i believe this is what you are looking for
--------------------
Book1
ABCDEFGHIJKLMN
1S. NoDate of VisitRegionBranchTown Party NameName of PartnerName of EmployeeIssue If Any (Yes/No)Reason for Non Satisfaction Remark Action need to be Taken Owner Date of closure
2144564NorthDelhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)ASC2CSE1NoPartner44564
3244564Noth Uttar Pradesh & UttarakhandTown4AAKRITI LIGHTING CO. ( INDIA)ASC2CSE1NoPartner44564
4344564EastBihar, Odisha & JharkhandTown4SHIV ELECTRIC EMPO.ASC3CSE2NoPartner44564
5444565Greater Punjab1BHARAT CROCKERY - 18
6
7
8
9
10
11Drop Down Prep
12
13BranchTownNameRegion
14Bihar, Odisha & JharkhandBiharTown1AAKRITI LIGHTING CO. ( INDIA)
15Delhi & NCRDelhiTown2
16Greater PunjabPunjabTown3
17GujaratGujaratTown4
18Uttar Pradesh & UttarakhandUttar
19
20Products
21
22BranchTownNameASCRegionCSE
23Bihar, Odisha & JharkhandTown3VATIKAASC2EastCSE1
24Bihar, Odisha & JharkhandTown3VICKY ELECTRICASC3EastCSE2
25Delhi & NCRTown43-STRIPSASC4NorthCSE1
26Delhi & NCRTown1A.A.ENTERPRISESASC2NorthCSE2
27Delhi & NCRTown3A.R.ELECTRICALSASC4NorthCSE1
28Delhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)ASC2NorthCSE1
29Greater PunjabTown1BHARAT CROCKERY - 18ASC3NorthCSE1
30Greater PunjabTown4BHAT ELECTRONICSASC3NorthCSE1
31Greater PunjabTown4BINDRA RADIOSASC2NorthCSE3
32GujaratTown2PRAKASH ELECTRONICSASC3WestCSE2
33GujaratTown3PRANAV ELE.ASC4WestCSE1
34GujaratTown3PRAVIN METAL CORPORATION,BARODAASC4WestCSE4
35GujaratTown1PUROHIT APPLIANCEASC3WestCSE2
36Uttar Pradesh & UttarakhandTown2INDIA LIGHT HOUSEASC2NorthCSE3
37Uttar Pradesh & UttarakhandTown1SHIV ELECTRIC EMPO.ASC3NorthCSE3
38Uttar Pradesh & UttarakhandTown2SINGHAL FURNITURESASC2NorthCSE2
main
Cells with Data Validation
CellAllowCriteria
D2:D5List=Branch
E5List=INDIRECT(HLOOKUP(D5,'branch tables'!$A$1:$E$2,2,0))
F5List=INDIRECT(SUBSTITUTE(VLOOKUP(D5,B14:C18,2)&E5," ",""))

---------------------
Book1
ABCDE
1Bihar, Odisha & JharkhandDelhi & NCRGreater PunjabGujaratUttar Pradesh & Uttarakhand
2BiharDelhiPunjabGujaratUttar
3
4BiharDelhiPunjabGujaratUttar
531111
62422
733
84
9
10Bihar3Delhi1Punjab1Gujarat1Uttar1
11VATIKAA.A.ENTERPRISESBHARAT CROCKERY - 18PUROHIT APPLIANCESHIV ELECTRIC EMPO.
12VICKY ELECTRIC
13
14
15
16Delhi2Punjab4Gujarat2Uttar2
17AAKRITI LIGHTING CO. ( INDIA)BHAT ELECTRONICSPRAKASH ELECTRONICSINDIA LIGHT HOUSE
18BINDRA RADIOSSINGHAL FURNITURES
19
20Delhi3Gujarat3
21A.R.ELECTRICALSPRANAV ELE.
22PRAVIN METAL CORPORATION,BARODA
23Delhi4
243-STRIPS
branch tables
Click to expand...
Hi ExceLoki,

Thanks for quick reply.

I am looking for more simple solution, i have shared sample data here, actual data is >500 towns i need to put and based on town i have to filter party (>3k Parties) hence creating branch table will not be that easy and hence i was looking if some how we can incorporate UNIQUE / FILETR FUNCTION and build some dynamic dropdown formula based on previous cell.

Sanket
 
Upvote 0
try this and let me know if you need any further assistance
------------
multiple dependant dropdowns.xlsx
ABCDEFGHIJKL
1BranchTownNameASCRegionCSEbranchesBranchTownName
2Bihar, Odisha & JharkhandTown3VATIKAASC2EastCSE1Bihar, Odisha & JharkhandBihar, Odisha & JharkhandTown3VATIKA
3Bihar, Odisha & JharkhandTown3VICKY ELECTRICASC3EastCSE2Delhi & NCRDelhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)
4Delhi & NCRTown1A.A.ENTERPRISESASC2NorthCSE2Greater PunjabGreater PunjabTown4BHAT ELECTRONICS
5Delhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)ASC2NorthCSE1GujaratGujaratTown3PRAVIN METAL CORPORATION,BARODA
6Delhi & NCRTown3A.R.ELECTRICALSASC4NorthCSE1Uttar Pradesh & UttarakhandUttar Pradesh & UttarakhandTown2SINGHAL FURNITURES
7Delhi & NCRTown43-STRIPSASC4NorthCSE1
8Greater PunjabTown1BHARAT CROCKERY - 18ASC3NorthCSE1
9Greater PunjabTown4BHAT ELECTRONICSASC3NorthCSE1
10Greater PunjabTown4BINDRA RADIOSASC2NorthCSE3
11GujaratTown1PUROHIT APPLIANCEASC3WestCSE2
12GujaratTown2PRAKASH ELECTRONICSASC3WestCSE2
13GujaratTown3PRANAV ELE.ASC4WestCSE1
14GujaratTown3PRAVIN METAL CORPORATION,BARODAASC4WestCSE4
15Uttar Pradesh & UttarakhandTown1SHIV ELECTRIC EMPO.ASC3NorthCSE3
16Uttar Pradesh & UttarakhandTown2INDIA LIGHT HOUSEASC2NorthCSE3
17Uttar Pradesh & UttarakhandTown2SINGHAL FURNITURESASC2NorthCSE2
Sheet2
Cell Formulas
RangeFormula
H2:H6H2=UNIQUE(A2:A17)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
J2:J17List=$H$2#
K2:K17List=OFFSET(A1,MATCH(J2,A2:A17,0)*1,1,COUNTIF(A2:A17,J2),1)
L2:L17List=OFFSET(OFFSET(A2, MATCH(J2,A2:A17,0)-1,1, COUNTIF(A2:A17,J2),1), MATCH(K2,OFFSET(A2, MATCH(J2,A2:A17,0)-1,1, COUNTIF(A2:A17,J2),1),0)-1,1, COUNTIF(OFFSET(A2, MATCH(J2,A2:A17,0)-1,1, COUNTIF(A2:A17,J2),1),K2),1)
 
Upvote 0
Solution
With some helper cells
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1S. NoDate of VisitRegion Town Party NaeName of PartnerName of EmployeeIssue If Any (Yes/No)Reason for Non Satisfaction Remark Action need to be Taken Owner Date of closure
2144564NorthDelhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)ASC2CSE1NoPartner44564Town1Town2Town3Town4AAKRITI LIGHTING CO. ( INDIA)
3244564Noth Uttar Pradesh & UttarakhandTown2INDIA LIGHT HOUSEASC2CSE1NoPartner44564Town1Town2INDIA LIGHT HOUSESINGHAL FURNITURES
4344564EastBihar, Odisha & JharkhandTown3SHIV ELECTRIC EMPO.ASC3CSE2NoPartner44564Town3VATIKAVICKY ELECTRIC
5  
6  
7  
8  
9  
10  
11Drop Down Prep
12
13BranchTownNameRegion
14Bihar, Odisha & Jharkhand
15Delhi & NCR
16Greater Punjab
17Gujarat
18Uttar Pradesh & Uttarakhand
19
20Products
21
22BranchTownNameASCRegionCSE
23Bihar, Odisha & JharkhandTown3VATIKAASC2EastCSE1
24Bihar, Odisha & JharkhandTown3VICKY ELECTRICASC3EastCSE2
25Delhi & NCRTown43-STRIPSASC4NorthCSE1
26Delhi & NCRTown1A.A.ENTERPRISESASC2NorthCSE2
27Delhi & NCRTown3A.R.ELECTRICALSASC4NorthCSE1
28Delhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)ASC2NorthCSE1
29Greater PunjabTown1BHARAT CROCKERY - 18ASC3NorthCSE1
30Greater PunjabTown4BHAT ELECTRONICSASC3NorthCSE1
31Greater PunjabTown4BINDRA RADIOSASC2NorthCSE3
32GujaratTown2PRAKASH ELECTRONICSASC3WestCSE2
33GujaratTown3PRANAV ELE.ASC4WestCSE1
34GujaratTown3PRAVIN METAL CORPORATION,BARODAASC4WestCSE4
35GujaratTown1PUROHIT APPLIANCEASC3WestCSE2
36Uttar Pradesh & UttarakhandTown2INDIA LIGHT HOUSEASC2NorthCSE3
37Uttar Pradesh & UttarakhandTown1SHIV ELECTRIC EMPO.ASC3NorthCSE3
38Uttar Pradesh & UttarakhandTown2SINGHAL FURNITURESASC2NorthCSE2
Main
Cell Formulas
RangeFormula
O2:R2,O4:O10,O3:P3O2=TRANSPOSE(SORT(UNIQUE(FILTER($C$23:$C$38,$B$23:$B$38=D2,""))))
U2,U5:U10,U3:V4U2=TRANSPOSE(SORT(UNIQUE(FILTER($D$23:$D$38,($B$23:$B$38=D2)*($C$23:$C$38=E2),""))))
B14:B18B14=UNIQUE(B23:B38)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
D2:D10List=$B$14#
E2:E10List=$O2#
F2:F10List=$U2#
 
Upvote 0
ExceLoki said:
try this and let me know if you need any further assistance
------------
multiple dependant dropdowns.xlsx
ABCDEFGHIJKL
1BranchTownNameASCRegionCSEbranchesBranchTownName
2Bihar, Odisha & JharkhandTown3VATIKAASC2EastCSE1Bihar, Odisha & JharkhandBihar, Odisha & JharkhandTown3VATIKA
3Bihar, Odisha & JharkhandTown3VICKY ELECTRICASC3EastCSE2Delhi & NCRDelhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)
4Delhi & NCRTown1A.A.ENTERPRISESASC2NorthCSE2Greater PunjabGreater PunjabTown4BHAT ELECTRONICS
5Delhi & NCRTown2AAKRITI LIGHTING CO. ( INDIA)ASC2NorthCSE1GujaratGujaratTown3PRAVIN METAL CORPORATION,BARODA
6Delhi & NCRTown3A.R.ELECTRICALSASC4NorthCSE1Uttar Pradesh & UttarakhandUttar Pradesh & UttarakhandTown2SINGHAL FURNITURES
7Delhi & NCRTown43-STRIPSASC4NorthCSE1
8Greater PunjabTown1BHARAT CROCKERY - 18ASC3NorthCSE1
9Greater PunjabTown4BHAT ELECTRONICSASC3NorthCSE1
10Greater PunjabTown4BINDRA RADIOSASC2NorthCSE3
11GujaratTown1PUROHIT APPLIANCEASC3WestCSE2
12GujaratTown2PRAKASH ELECTRONICSASC3WestCSE2
13GujaratTown3PRANAV ELE.ASC4WestCSE1
14GujaratTown3PRAVIN METAL CORPORATION,BARODAASC4WestCSE4
15Uttar Pradesh & UttarakhandTown1SHIV ELECTRIC EMPO.ASC3NorthCSE3
16Uttar Pradesh & UttarakhandTown2INDIA LIGHT HOUSEASC2NorthCSE3
17Uttar Pradesh & UttarakhandTown2SINGHAL FURNITURESASC2NorthCSE2
Sheet2
Cell Formulas
RangeFormula
H2:H6H2=UNIQUE(A2:A17)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
J2:J17List=$H$2#
K2:K17List=OFFSET(A1,MATCH(J2,A2:A17,0)*1,1,COUNTIF(A2:A17,J2),1)
L2:L17List=OFFSET(OFFSET(A2, MATCH(J2,A2:A17,0)-1,1, COUNTIF(A2:A17,J2),1), MATCH(K2,OFFSET(A2, MATCH(J2,A2:A17,0)-1,1, COUNTIF(A2:A17,J2),1),0)-1,1, COUNTIF(OFFSET(A2, MATCH(J2,A2:A17,0)-1,1, COUNTIF(A2:A17,J2),1),K2),1)
Click to expand...
---------
as a note, this does require the first 2 columns in the source data table to be sorted. in my example above i sorted by branch then by town.
 
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Dynamic dropdown list based on database
Replies
3
Views
219
Winnetou
W
W
  • Question
contents of dropdownlist dependant on cell value
Replies
6
Views
279
Akuini
Akuini
S
  • Question
VBA to Hide Unhide sheets based on dropdown selection.
Replies
1
Views
562
Micron
M
G
  • Question
VBA or v/x lookup to populate a table with data, based on selection of an adjacent dropdown?
Replies
1
Views
383
Guitarmageddon
G
L
  • Solved
Finding cell based on a dropdown - using INDEX function
Replies
7
Views
682
LucieLiskova
L

Forum statistics

Threads
1,215,575
Messages
6,125,631
Members
449,241
Latest member
NoniJ

Share this page

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
Back
Top