• 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.

Create a main dropdown list based on another sub-menu

Hany ali

Active Member
hello my masters ,i want your Help to Create a main dropdown list based on another sub-menu .... as Example in Sheet Result
because I Used this Formula in Dropdown List ,but Not Work With Me
Code:
=OFFSET(Prices!$A$3,1,MATCH($C2,Prices!$A$2:$D$2,0)-1,COUNTA(OFFSET(Prices!$A$2,1,MATCH($C2,Prices!$A$2:$D$2,0)-1,20,1)),1)
 

Attachments

bosco_yip

Excel Ninja
Perhaps,

In cell B2, enter formula (formula Not in dropdown list!) copied down ;

=IF($C2="","",INDEX(Prices!$A$2:$D$2,SUMPRODUCT(COLUMN($A$1:$D$1)*(Prices!$A$3:$D$1000=$C2))))

66315

Regards
Bosco
 

Hany ali

Active Member
thanks alot
@bosco_yip
But if Possible in dropdown list ,because i have alot of data in Sub Menu the Same with another Sub List ...but in Differant Main List
 

bosco_yip

Excel Ninja
thanks alot
@bosco_yip
But if Possible in dropdown list ,because i have alot of data in Sub Menu the Same with another Sub List ...but in Differant Main List
What do you wish to return ?

For example in B2, you can only have one answer (not multiple answers, the other main list answers are wrong), B2 can change to other result in according to C2 dropdown list in other selection.

Regards
Bosco
 

bosco_yip

Excel Ninja
I think you wish to have anther option setup.

Here is a 2 level dropdown list example.

1] In "Main Menu" B2 >> Data Validation
>> Allow : List
>> Source : =Prices!$A$2:$D$2
>> OK >> Finish

2] In "Sub Menu List" C2 >> Data Validation
>> Allow : List
>> Source : =OFFSET(Prices!$A$3,0,MATCH($B2,Prices!$A$2:$D$2,0)-1,COUNTA(OFFSET(Prices!$A$3,0,MATCH($B2,Prices!$A$2:$D$2,0)-1,20)))
>> OK >> Finish

3] Then, all copied down

4] Please see attachment

Regards
Bosco
 

Attachments

Hany ali

Active Member
thanks my Dear for your Helping,but i Want Opposite of this as i explan to you before
because i have allready my sub menu list from our system It is assumed that I choose what suits her and corresponds to this item from the main menu that includes Hospital ,Street,.... etc
i hope to be Clear ,thanks
 

bosco_yip

Excel Ninja
Just gussing, does this what you want ?

If C2 : "Ihab", B2 show the list consist of "Ihab"

66369

Then,

'In "Main Menu" B2 >> Data Validation

>> Allow : List

>> Source : =OFFSET(Prices!$A$3,0,SUMPRODUCT(COLUMN($A$1:$D$1)*(Prices!$A$3:$D$1000=$C2))-1,COUNTA(OFFSET(Prices!$A$3,0,SUMPRODUCT(COLUMN($A$1:$D$1)*(Prices!$A$3:$D$1000=$C2))-1,20)))

>> OK >> Finish

and, copied down

Regards
 

Attachments

Last edited:

Hany ali

Active Member
YES thanks very much .... Of course this is required
but when i have sharing in the same Name in differant main list ,i haven't any data in dropdown list... but of course i want to see all data in the main list
 

Attachments

Last edited:

bosco_yip

Excel Ninja
1] If you have duplicated sub-menu name, like "Ihab" appear both in "School" and "Company" list

2] An array formula result could be work out like {"School";"Company"}, but "Validation drop down List" don't accept array formula.

So,

3.1] The workaround way is to create a new "Main menu and Sub menu list" as in M1:N14

3.2] In "Main Menu" B2 >> Data Validation
>> Allow : List
>> Source : =OFFSET(Prices!$N$2,MATCH($C2,Prices!$M$2:$M$100,0),,COUNTIF(Prices!$M$2:$M$100,$C2))
>> OK >> Finish
and, copied down.

66400

Regards
 

Attachments

Hany ali

Active Member
I apologize a lot for the trouble of you
You are truly a genius ... exactly that is required
Thank you very much for your kindness with me
 
Top