• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiple dependencies for a drop down list

E

ELL

New Member
I have a list with four items and each one of those items, when selected, could have a list of choices. So lets say you choose Pizza from the first list, and it the second column list dropdown display veggie, cheese, ham
Then if you pick the second item in list one, say Chicken, the next column drop down will display sandwich, rice bowl.
So, I want to do this with four different items from column one. Whichever is selected, then the associated, appropriate choices are in the next column drop down list.
Is this possible? I have done a single dependency dropdown, but not multiple.
 
Hi Ell,

Have of look of attached file. Is this what you want?

Regards!
 

Attachments

  • ELL Data Validation.xlsx
    9.2 KB · Views: 15
Hi Ell,

The link provided by @SirJB7 works great, and I myself have tried it out for 3 instance validation and I am quite sure this should work for more validations as well...

If you can provide us with what have you actually tried out from that link that will help in providing better and precise...
 
@SirJB7 : This is a VBA approach, that is just data validation with indirect formulas....

@ELL :

So here is what you do:

A1:Cuisine
A2: Food Item
A3: Food Type

G1:G2:-Sea.Food,Chinese (Range Named:- Cuisine)
H1:H2:- Prawns, Fish (Range Named:- Sea.Food)
I1:I2:- Soups, Noodles (Range Named:- Chinese)
J1:J2:- Curry, Fried (Range Named:- Prawns)
K1:K2:- Pomfret (Range Named:- Fish)
L1:L2:- Hot n Sour, Sweet Corn (Range Named:- Soups)
M1:M2:- Shezwan, Singaporian (Range Named:- Noodles)

Data Validations:
cell B1: DV type:- List Range:- "=Cuisine"
cell B2: DV type:- List Range:- "=Indirect(B1)"
cell B3: DV type:- List Range:- "=Indirect(B2)"
 
ELL said:
The site didn't reall

Not to much. I cannot use VB, because of certain restrictions. I need to use just formulas. BAut I have need for a 4 dependency - choose A, B, C, D, and each one of those choices will have a selection. I hav ebeen successbul with two butr not four.
Click to expand...
Somendra Misra said:
Hi Ell,

Have of look of attached file. Is this what you want?

Regards!
Click to expand...

Yes, the setup is what I want, it's just that I cannot seem to implement it beyond two drop down list . I have one column, but what I select does not seem to list it's corresponding drop down. I'm using indirect, but i may have something wron.
 
Hi, ELL!
If you're willing to upload your actual file, or at least the empty structure with the minimum data for implementing the 4 drop down lists with DV, probably someone will be able to help you thru that.
Regards!
 
Hi Ell,
Look at attached, please remember you need range names for all list, the range names in this example are not dynamic, but they can be made dynamic with the Offset and Count functions.

Let mo know if this is ok
 

Attachments

  • ELL Data Validation2.xlsx
    9.4 KB · Views: 4
Hi Ell,

Have you checked the file I uploaded earlier. It is using various named ranges wgich are having sub categories & INDIRECT function. Have a look at it and advise if you have any issue.

Regards!
 
SirJB7 said:
Hi, ELL!
If you're willing to upload your actual file, or at least the empty structure with the minimum data for implementing the 4 drop down lists with DV, probably someone will be able to help you thru that.
Regards!
Click to expand...

Do you think you can just use formulas, and not VBA to do the dropdown lists?
 

Attachments

  • ExWithDropdowns.xlsx
    14.6 KB · Views: 4
Hi Ell,

Have you looked at the file I sent, it has no VBA only formulas and named ranges,

Have updated your file
 
Last edited:
Hi Ell,

Here is you file updated, you will note that I had to change the Status header on the hide sheet.

Do you still require a further layer of dropdowns?, if so please send a file with an example of the end result and all the lists involved.

regards

kanti
 

Attachments

  • ExWithDropdowns1.xlsx
    14.6 KB · Views: 15
@SirJB7 ... Hi....it is working partially...because I had made a sample to demonstrate the technique. The method can be repeated for all the entries.

Regards!
 
@Somendra Misra
Hi!
I supposed so, but having to give an answer to the OP who didn't manage to handle he previous uploaded files I considered that I should point out that so as to don't confuse him any more.
Whenever you post a partial solution, please state it clear so as people who read this knows what happen and what might expect. Thank you.
Regards!
 
Good day Ell

Please have a look at the upload it may help you.

When you create your named range lists tutn them into a table that way if you add to them they will auto update the drop downs.



 

Attachments

  • DataValidation.xls
    135 KB · Views: 11
Last edited:
kchiba said:
Hi Ell,

Here is you file updated, you will note that I had to change the Status header on the hide sheet.

Do you still require a further layer of dropdowns?, if so please send a file with an example of the end result and all the lists involved.

regards

kanti
Click to expand...

Thank you! This is what I was trying to accomplish without using VBA. Now I see where I was going wrong w/my dorp down lists. Thanks again.
 
SirJB7 said:
Hi, ELL!

I've checked both files from Somendra Misra's at 4th post:
http://chandoo.org/forum/threads/multiple-dependencies-for-a-drop-down-list.13889/#post-82245
and from kchiba's last 15th post:
http://chandoo.org/forum/threads/multiple-dependencies-for-a-drop-down-list.13889/#post-82493-

The 1st one works partially, for the 1st entries only (or so), but the 2nd one works fine.

Have you downloaded and tested it?

Regards!
Click to expand...

Yes, I have used kchiba's and it works perfectly. I did not respond sooner as I was out/sick, but much better now. kchib's solution works great.
 
Hi, ELL!
Glad you solved it. Most credit to kchiba then. Thanks for your feedback and hope you'll be doing fine versy soon. And welcome back whenever needed or wanted.
Regards!
 
You must log in or register to reply here.
Back
Top