# 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

• 25.4 KB Views: 5
• 29.7 KB Views: 5

#### 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))))

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

Regards
Bosco

#### Attachments

• 21.7 KB Views: 5

#### 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

#### Hany ali

##### Active Member
please Is there any solution ?

#### bosco_yip

##### Excel Ninja
Just gussing, does this what you want ?

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

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

• 28.2 KB Views: 2
Last edited:

#### Hany ali

##### Active Member
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

• 30 KB Views: 2
• 17.4 KB Views: 2

#### bosco_yip

##### Excel Ninja
I think this maybe what you need !

Regards

#### Attachments

• 28.5 KB Views: 2

#### 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

• 44.6 KB Views: 1
• 17.4 KB Views: 2
• 29 KB Views: 0
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.

Regards

#### Attachments

• 38.5 KB Views: 1

#### 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