JavaScript is disabled. For a better experience, please enable JavaScript in your browser before proceeding.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an
alternative browser .
Dependent Dropdown based on previous cell selection
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. No Date of Visit Region Town Party Nae Name of Partner Name of Employee Issue If Any (Yes/No) Reason for Non Satisfaction Remark Action need to be Taken Owner Date of closure 1 3-Jan-22 North Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) ASC2 CSE1 No Partner 03-Jan-22 2 3-Jan-22 Noth Uttar Pradesh & Uttarakhand Town4 AAKRITI LIGHTING CO. ( INDIA) ASC2 CSE1 No Partner 03-Jan-22 3 3-Jan-22 East Bihar, Odisha & Jharkhand Town4 SHIV ELECTRIC EMPO. ASC3 CSE2 No Partner 03-Jan-22 Drop Down Prep Branch Town Name Region Bihar, Odisha & Jharkhand Town1 AAKRITI LIGHTING CO. ( INDIA) Delhi & NCR Town2 Greater Punjab Town3 Gujarat Town4 Uttar Pradesh & Uttarakhand Products Branch Town Name ASC Region CSE Bihar, Odisha & Jharkhand Town3 VATIKA ASC2 East CSE1 Bihar, Odisha & Jharkhand Town3 VICKY ELECTRIC ASC3 East CSE2 Delhi & NCR Town4 3-STRIPS ASC4 North CSE1 Delhi & NCR Town1 A.A.ENTERPRISES ASC2 North CSE2 Delhi & NCR Town3 A.R.ELECTRICALS ASC4 North CSE1 Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) ASC2 North CSE1 Greater Punjab Town1 BHARAT CROCKERY - 18 ASC3 North CSE1 Greater Punjab Town4 BHAT ELECTRONICS ASC3 North CSE1 Greater Punjab Town4 BINDRA RADIOS ASC2 North CSE3 Gujarat Town2 PRAKASH ELECTRONICS ASC3 West CSE2 Gujarat Town3 PRANAV ELE. ASC4 West CSE1 Gujarat Town3 PRAVIN METAL CORPORATION,BARODA ASC4 West CSE4 Gujarat Town1 PUROHIT APPLIANCE ASC3 West CSE2 Uttar Pradesh & Uttarakhand Town2 INDIA LIGHT HOUSE ASC2 North CSE3 Uttar Pradesh & Uttarakhand Town1 SHIV ELECTRIC EMPO. ASC3 North CSE3 Uttar Pradesh & Uttarakhand Town2 SINGHAL FURNITURES ASC2 North CSE2
i believe this is what you are looking for
--------------------
Book1 A B C D E F G H I J K L M N 1 S. No Date of Visit Region Branch Town Party Name Name of Partner Name of Employee Issue If Any (Yes/No) Reason for Non Satisfaction Remark Action need to be Taken Owner Date of closure 2 1 44564 North Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) ASC2 CSE1 No Partner 44564 3 2 44564 Noth Uttar Pradesh & Uttarakhand Town4 AAKRITI LIGHTING CO. ( INDIA) ASC2 CSE1 No Partner 44564 4 3 44564 East Bihar, Odisha & Jharkhand Town4 SHIV ELECTRIC EMPO. ASC3 CSE2 No Partner 44564 5 4 44565 Greater Punjab 1 BHARAT CROCKERY - 18 6 7 8 9 10 11 Drop Down Prep 12 13 Branch Town Name Region 14 Bihar, Odisha & Jharkhand Bihar Town1 AAKRITI LIGHTING CO. ( INDIA) 15 Delhi & NCR Delhi Town2 16 Greater Punjab Punjab Town3 17 Gujarat Gujarat Town4 18 Uttar Pradesh & Uttarakhand Uttar 19 20 Products 21 22 Branch Town Name ASC Region CSE 23 Bihar, Odisha & Jharkhand Town3 VATIKA ASC2 East CSE1 24 Bihar, Odisha & Jharkhand Town3 VICKY ELECTRIC ASC3 East CSE2 25 Delhi & NCR Town4 3-STRIPS ASC4 North CSE1 26 Delhi & NCR Town1 A.A.ENTERPRISES ASC2 North CSE2 27 Delhi & NCR Town3 A.R.ELECTRICALS ASC4 North CSE1 28 Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) ASC2 North CSE1 29 Greater Punjab Town1 BHARAT CROCKERY - 18 ASC3 North CSE1 30 Greater Punjab Town4 BHAT ELECTRONICS ASC3 North CSE1 31 Greater Punjab Town4 BINDRA RADIOS ASC2 North CSE3 32 Gujarat Town2 PRAKASH ELECTRONICS ASC3 West CSE2 33 Gujarat Town3 PRANAV ELE. ASC4 West CSE1 34 Gujarat Town3 PRAVIN METAL CORPORATION,BARODA ASC4 West CSE4 35 Gujarat Town1 PUROHIT APPLIANCE ASC3 West CSE2 36 Uttar Pradesh & Uttarakhand Town2 INDIA LIGHT HOUSE ASC2 North CSE3 37 Uttar Pradesh & Uttarakhand Town1 SHIV ELECTRIC EMPO. ASC3 North CSE3 38 Uttar Pradesh & Uttarakhand Town2 SINGHAL FURNITURES ASC2 North CSE2
---------------------
Book1 A B C D E 1 Bihar, Odisha & Jharkhand Delhi & NCR Greater Punjab Gujarat Uttar Pradesh & Uttarakhand 2 Bihar Delhi Punjab Gujarat Uttar 3 4 Bihar Delhi Punjab Gujarat Uttar 5 3 1 1 1 1 6 2 4 2 2 7 3 3 8 4 9 10 Bihar3 Delhi1 Punjab1 Gujarat1 Uttar1 11 VATIKA A.A.ENTERPRISES BHARAT CROCKERY - 18 PUROHIT APPLIANCE SHIV ELECTRIC EMPO. 12 VICKY ELECTRIC 13 14 15 16 Delhi2 Punjab4 Gujarat2 Uttar2 17 AAKRITI LIGHTING CO. ( INDIA) BHAT ELECTRONICS PRAKASH ELECTRONICS INDIA LIGHT HOUSE 18 BINDRA RADIOS SINGHAL FURNITURES 19 20 Delhi3 Gujarat3 21 A.R.ELECTRICALS PRANAV ELE. 22 PRAVIN METAL CORPORATION,BARODA 23 Delhi4 24 3-STRIPS
i believe this is what you are looking for
--------------------
Book1 A B C D E F G H I J K L M N 1 S. No Date of Visit Region Branch Town Party Name Name of Partner Name of Employee Issue If Any (Yes/No) Reason for Non Satisfaction Remark Action need to be Taken Owner Date of closure 2 1 44564 North Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) ASC2 CSE1 No Partner 44564 3 2 44564 Noth Uttar Pradesh & Uttarakhand Town4 AAKRITI LIGHTING CO. ( INDIA) ASC2 CSE1 No Partner 44564 4 3 44564 East Bihar, Odisha & Jharkhand Town4 SHIV ELECTRIC EMPO. ASC3 CSE2 No Partner 44564 5 4 44565 Greater Punjab 1 BHARAT CROCKERY - 18 6 7 8 9 10 11 Drop Down Prep 12 13 Branch Town Name Region 14 Bihar, Odisha & Jharkhand Bihar Town1 AAKRITI LIGHTING CO. ( INDIA) 15 Delhi & NCR Delhi Town2 16 Greater Punjab Punjab Town3 17 Gujarat Gujarat Town4 18 Uttar Pradesh & Uttarakhand Uttar 19 20 Products 21 22 Branch Town Name ASC Region CSE 23 Bihar, Odisha & Jharkhand Town3 VATIKA ASC2 East CSE1 24 Bihar, Odisha & Jharkhand Town3 VICKY ELECTRIC ASC3 East CSE2 25 Delhi & NCR Town4 3-STRIPS ASC4 North CSE1 26 Delhi & NCR Town1 A.A.ENTERPRISES ASC2 North CSE2 27 Delhi & NCR Town3 A.R.ELECTRICALS ASC4 North CSE1 28 Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) ASC2 North CSE1 29 Greater Punjab Town1 BHARAT CROCKERY - 18 ASC3 North CSE1 30 Greater Punjab Town4 BHAT ELECTRONICS ASC3 North CSE1 31 Greater Punjab Town4 BINDRA RADIOS ASC2 North CSE3 32 Gujarat Town2 PRAKASH ELECTRONICS ASC3 West CSE2 33 Gujarat Town3 PRANAV ELE. ASC4 West CSE1 34 Gujarat Town3 PRAVIN METAL CORPORATION,BARODA ASC4 West CSE4 35 Gujarat Town1 PUROHIT APPLIANCE ASC3 West CSE2 36 Uttar Pradesh & Uttarakhand Town2 INDIA LIGHT HOUSE ASC2 North CSE3 37 Uttar Pradesh & Uttarakhand Town1 SHIV ELECTRIC EMPO. ASC3 North CSE3 38 Uttar Pradesh & Uttarakhand Town2 SINGHAL FURNITURES ASC2 North CSE2
---------------------
Book1 A B C D E 1 Bihar, Odisha & Jharkhand Delhi & NCR Greater Punjab Gujarat Uttar Pradesh & Uttarakhand 2 Bihar Delhi Punjab Gujarat Uttar 3 4 Bihar Delhi Punjab Gujarat Uttar 5 3 1 1 1 1 6 2 4 2 2 7 3 3 8 4 9 10 Bihar3 Delhi1 Punjab1 Gujarat1 Uttar1 11 VATIKA A.A.ENTERPRISES BHARAT CROCKERY - 18 PUROHIT APPLIANCE SHIV ELECTRIC EMPO. 12 VICKY ELECTRIC 13 14 15 16 Delhi2 Punjab4 Gujarat2 Uttar2 17 AAKRITI LIGHTING CO. ( INDIA) BHAT ELECTRONICS PRAKASH ELECTRONICS INDIA LIGHT HOUSE 18 BINDRA RADIOS SINGHAL FURNITURES 19 20 Delhi3 Gujarat3 21 A.R.ELECTRICALS PRANAV ELE. 22 PRAVIN METAL CORPORATION,BARODA 23 Delhi4 24 3-STRIPS
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
try this and let me know if you need any further assistance
------------
multiple dependant dropdowns.xlsx A B C D E F G H I J K L 1 Branch Town Name ASC Region CSE branches Branch Town Name 2 Bihar, Odisha & Jharkhand Town3 VATIKA ASC2 East CSE1 Bihar, Odisha & Jharkhand Bihar, Odisha & Jharkhand Town3 VATIKA 3 Bihar, Odisha & Jharkhand Town3 VICKY ELECTRIC ASC3 East CSE2 Delhi & NCR Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) 4 Delhi & NCR Town1 A.A.ENTERPRISES ASC2 North CSE2 Greater Punjab Greater Punjab Town4 BHAT ELECTRONICS 5 Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) ASC2 North CSE1 Gujarat Gujarat Town3 PRAVIN METAL CORPORATION,BARODA 6 Delhi & NCR Town3 A.R.ELECTRICALS ASC4 North CSE1 Uttar Pradesh & Uttarakhand Uttar Pradesh & Uttarakhand Town2 SINGHAL FURNITURES 7 Delhi & NCR Town4 3-STRIPS ASC4 North CSE1 8 Greater Punjab Town1 BHARAT CROCKERY - 18 ASC3 North CSE1 9 Greater Punjab Town4 BHAT ELECTRONICS ASC3 North CSE1 10 Greater Punjab Town4 BINDRA RADIOS ASC2 North CSE3 11 Gujarat Town1 PUROHIT APPLIANCE ASC3 West CSE2 12 Gujarat Town2 PRAKASH ELECTRONICS ASC3 West CSE2 13 Gujarat Town3 PRANAV ELE. ASC4 West CSE1 14 Gujarat Town3 PRAVIN METAL CORPORATION,BARODA ASC4 West CSE4 15 Uttar Pradesh & Uttarakhand Town1 SHIV ELECTRIC EMPO. ASC3 North CSE3 16 Uttar Pradesh & Uttarakhand Town2 INDIA LIGHT HOUSE ASC2 North CSE3 17 Uttar Pradesh & Uttarakhand Town2 SINGHAL FURNITURES ASC2 North CSE2
With some helper cells
+Fluff 1.xlsm A B C D E F G H I J K L M N O P Q R S T U V W 1 S. No Date of Visit Region Town Party Nae Name of Partner Name of Employee Issue If Any (Yes/No) Reason for Non Satisfaction Remark Action need to be Taken Owner Date of closure 2 1 44564 North Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) ASC2 CSE1 No Partner 44564 Town1 Town2 Town3 Town4 AAKRITI LIGHTING CO. ( INDIA) 3 2 44564 Noth Uttar Pradesh & Uttarakhand Town2 INDIA LIGHT HOUSE ASC2 CSE1 No Partner 44564 Town1 Town2 INDIA LIGHT HOUSE SINGHAL FURNITURES 4 3 44564 East Bihar, Odisha & Jharkhand Town3 SHIV ELECTRIC EMPO. ASC3 CSE2 No Partner 44564 Town3 VATIKA VICKY ELECTRIC 5 6 7 8 9 10 11 Drop Down Prep 12 13 Branch Town Name Region 14 Bihar, Odisha & Jharkhand 15 Delhi & NCR 16 Greater Punjab 17 Gujarat 18 Uttar Pradesh & Uttarakhand 19 20 Products 21 22 Branch Town Name ASC Region CSE 23 Bihar, Odisha & Jharkhand Town3 VATIKA ASC2 East CSE1 24 Bihar, Odisha & Jharkhand Town3 VICKY ELECTRIC ASC3 East CSE2 25 Delhi & NCR Town4 3-STRIPS ASC4 North CSE1 26 Delhi & NCR Town1 A.A.ENTERPRISES ASC2 North CSE2 27 Delhi & NCR Town3 A.R.ELECTRICALS ASC4 North CSE1 28 Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) ASC2 North CSE1 29 Greater Punjab Town1 BHARAT CROCKERY - 18 ASC3 North CSE1 30 Greater Punjab Town4 BHAT ELECTRONICS ASC3 North CSE1 31 Greater Punjab Town4 BINDRA RADIOS ASC2 North CSE3 32 Gujarat Town2 PRAKASH ELECTRONICS ASC3 West CSE2 33 Gujarat Town3 PRANAV ELE. ASC4 West CSE1 34 Gujarat Town3 PRAVIN METAL CORPORATION,BARODA ASC4 West CSE4 35 Gujarat Town1 PUROHIT APPLIANCE ASC3 West CSE2 36 Uttar Pradesh & Uttarakhand Town2 INDIA LIGHT HOUSE ASC2 North CSE3 37 Uttar Pradesh & Uttarakhand Town1 SHIV ELECTRIC EMPO. ASC3 North CSE3 38 Uttar Pradesh & Uttarakhand Town2 SINGHAL FURNITURES ASC2 North CSE2
try this and let me know if you need any further assistance
------------
multiple dependant dropdowns.xlsx A B C D E F G H I J K L 1 Branch Town Name ASC Region CSE branches Branch Town Name 2 Bihar, Odisha & Jharkhand Town3 VATIKA ASC2 East CSE1 Bihar, Odisha & Jharkhand Bihar, Odisha & Jharkhand Town3 VATIKA 3 Bihar, Odisha & Jharkhand Town3 VICKY ELECTRIC ASC3 East CSE2 Delhi & NCR Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) 4 Delhi & NCR Town1 A.A.ENTERPRISES ASC2 North CSE2 Greater Punjab Greater Punjab Town4 BHAT ELECTRONICS 5 Delhi & NCR Town2 AAKRITI LIGHTING CO. ( INDIA) ASC2 North CSE1 Gujarat Gujarat Town3 PRAVIN METAL CORPORATION,BARODA 6 Delhi & NCR Town3 A.R.ELECTRICALS ASC4 North CSE1 Uttar Pradesh & Uttarakhand Uttar Pradesh & Uttarakhand Town2 SINGHAL FURNITURES 7 Delhi & NCR Town4 3-STRIPS ASC4 North CSE1 8 Greater Punjab Town1 BHARAT CROCKERY - 18 ASC3 North CSE1 9 Greater Punjab Town4 BHAT ELECTRONICS ASC3 North CSE1 10 Greater Punjab Town4 BINDRA RADIOS ASC2 North CSE3 11 Gujarat Town1 PUROHIT APPLIANCE ASC3 West CSE2 12 Gujarat Town2 PRAKASH ELECTRONICS ASC3 West CSE2 13 Gujarat Town3 PRANAV ELE. ASC4 West CSE1 14 Gujarat Town3 PRAVIN METAL CORPORATION,BARODA ASC4 West CSE4 15 Uttar Pradesh & Uttarakhand Town1 SHIV ELECTRIC EMPO. ASC3 North CSE3 16 Uttar Pradesh & Uttarakhand Town2 INDIA LIGHT HOUSE ASC2 North CSE3 17 Uttar Pradesh & Uttarakhand Town2 SINGHAL FURNITURES ASC2 North CSE2
---------
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.