• 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

  • 1.png
    1.png
    25.4 KB · Views: 7
  • Drop Down List.xlsm
    29.7 KB · Views: 5
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
 
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
 
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
 
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

  • Drop Down List(BY).xlsx
    21.7 KB · Views: 10
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
 
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

  • Drop Down List (2).xlsm
    28.2 KB · Views: 2
Last edited:
thanks alot my sir
but i want the List in Column B like this File ,but without INCLUDING Sub Menu ,just by main Menu
 

Attachments

  • Drop Down List (2).xlsm
    30 KB · Views: 2
  • 1.png
    1.png
    17.4 KB · Views: 2
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

  • 1.png
    1.png
    44.6 KB · Views: 1
  • 2.png
    2.png
    17.4 KB · Views: 2
  • Drop Down List (3).xlsm
    29 KB · Views: 0
Last edited:
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

  • Drop Down List (4).xlsm
    38.5 KB · Views: 4
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
 
Back
Top