Create a main dropdown list based on another sub-menu

[Hany ali]

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

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

 

[bosco_yip]

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]

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]

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]

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

 

[Hany ali]

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]

Up Please

 

[Hany ali]

please Is there any solution ?

 

[bosco_yip]

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

 

[Hany ali]

thanks alot my sir
but i want the List in Column B like this File ,but without INCLUDING Sub Menu ,just by main Menu

 

[bosco_yip]

I think this maybe what you need !



Regards

 

[Hany ali]

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

 

[bosco_yip]

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

 

[Hany ali]

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