Excel Dependent Drop Down List with Repetitive Values

Question

I have some data stored in an hierarchical way like this: Data Stored Hierarchical

And I want to create three drop down lists from where you can select the Product in the Category based on the Store,Something like this: Drop Down depended list

The tricky thing here is the fact that a product(i.e Frozen Pizza) can be found on both stores while others (Lays) can only be found on one store. How can I achieve this or how can I store the data in such a way that I can have the same result?.

I've tried things like named range with the data stored in a table like structure and with =INDIRECT (but won't work because of illegal characters like spaces,symbols,etc in the named range).I am looking for a Formula not a VBA.

You're asking how to acheiive different lists? By populating combo box number two after combo box number one changes. This is obviously homework, right? Are you going to be using VBA to accomplish this? It's probably easiest... unless you've never used VBA, in which case you're going to have to have the values in the item list range on a worksheet change automatically when the cell link changes, maybe with VLookup. The first thing you need to do is begin putting it together yourself and show what you have. If you don't know how to use combo boxes then find a few tutorials. — ashleedawg
– ashleedawg, Commented Mar 18, 2018 at 0:02

Seth-David Wilson · Accepted Answer · 2018-03-19 02:13:09Z

1

I think you were on the right track. If not using VBA, I would use data stored in a table with named ranges and the INDIRECT formula.

That approach would be arduous as you would have to build out each list in its own range (e.g. products in category 1 of store 1, products in category 2 of store 1, etc.).

Also, as you mentioned, the named ranges are strict, so you would need to convert spaces and symbols to _ or omit them completely. You could consider using numeric IDs in the drop down lists instead of text, but the user would need to know what the IDs represent. You could then translate the IDs back to text using a lookup table once selected.

VBA would certainly provide a better solution.

answered Mar 19, 2018 at 2:13

Seth-David Wilson

263 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel Dependent Drop Down List with Repetitive Values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related