Data validation list / sublist

[Mryam]

Hi, is there a way for haveing a dependant list but in the same cell. For example here Excel Dependent Drop-down Lists - Easy Excel Tutorial

The list are it two different cell. What i need is that for example in the same cell i can see a multi level list.

I hope i was clear in explaining becaue i googled this with multiuple keyword with no result i figured it had a name that i dont know.

Thanks for the help this is very important for my work

 

[Mryam]

Hi, no one can help?

 

[MarcelBeug]

Do you mean in one cell you have a dropdown list with choices from the top level, once you select the top level, you can select from the next lower level?
Example: first you select Food: Chinese or Italian. Once you choose Chinese, in the same cell you select Bami or Nasi, etcetera.

This is possible (like in the link you provided), but you will need a defined name for each and every possible selection. And you need to include some higher level in the list. E.g. the choices for Chinese are Bami, Nasi and Food. This list has defined names "Chinese", "Bami" and "Nasi".
In the INDIRECT function mentioned in the link, you must provide the cell with the dropdown box.

Hope this is clear or at least a step in the desired direction.

 

[Mryam]

Hi, thanks for your replay it is more like this.

but what i need to happen is when i click the cell i get a menu that i can follw through a submenu and when clicking the item in the sub menu it will show in the cell
hope i am clear enough.
sorry if i am not

 

[Mryam]

How to create a multi level data validaiton list.
Example:
Food

Burger
Pizza
Pasta​

Drink

Soda
Water
Juice​

So when i click the drop down menu i see only food and drink but when i hover on food i can see the rest which is burger, pizza, pasta.
Just to clarify this need to be in the same cell not like the dependent drop down list.
Thanks for the help

 

[MarcelBeug]

Edit: this was posted concurrent with the previous post. I will come back on that in a moment.

Hi, OK it's clear to me.
Basically it's similar to my suggestion, except that you want to keep the items of each level visible while navigating through the submenus.

I don't think this will be possible. At least not without VBA.

As my VBA knowledge is very limited, I think this would be the right point for a VBA expert to join this topic and advise or provide a VBA solution.

 

[MarcelBeug]

For any validation list I would recommend to format the list as a table, so it will automatically expand when items are added or shrink if items are deleted.
Tables to be separated by a blank column.

Top list, formatted as Table1 with header Product (in A1):
A1: Product
A2: Food
A3: Drink
Defined name Product for Table1[Product].
This is equal to A2:A3.

Food list, formatted as Table2 with header Food (in C1):
C1: Food
C2: Burger
C3: Pizza
C4: Pasta
C5: Product
As exlained in post #3, you need the top level in each list, otherwise you’ll never be able to navigate back to the top.
Defined names, each for Table2[Food], which is C2:C5:
Food, Burger, Pizza, Pasta

Drink list, formatted as Table3 with header Drink (in E1):
E1: Drink
E2: Soda
E3: Water
E4: Juice
E5: Product.
Defined names, each for Table3[Drink], which is E2:E5:
Drink, Soda, Water, Juice

On another worksheet, I have cell B1 with data validation against a list with source:
=INDIRECT($B$1)

E.g. when B1 = Product, then the dropdown list will contain the elements of defined name “Product”: Food and Drink.
When you select Food, then the next dropdown list, still for cell B1, will contain the elements of defined name “Food”: Burger, Pizza, Pasta and Product.
Etcetera.

If you add another drink, e.g. Coffee, then you must also define name Coffee for range Table3[Drink], which is now E2:E6.
The range for the other defined names for Table3[Drink] are automatically adjusted by Excel, because the range is a table.
When you delete an Item, you should also delete the associated defined name.

Hope this is all clear and helpful.