Using two dropdown lists, to generate another list from a list

asked 2019-02-19 21:36:18 +0100

Miletech gravatar image

updated 2019-03-03 05:03:17 +0100

Yep. I am the master at easily understood titles.

Ok, basic premise:

If I have two drop down menus using the Data>Validity>List function to establish my parameters. Let's call first 'Restaurants' and the second 'Amenities'

List 1: Restaurants McDonald's Burger King Chuck-E-Cheese's Mac's BBQ Brenda's Corner Tavern

List 2: Amenities Simple Advanced

If I selected Mac's BBQ and Simple, I'd get a list like this

Dine-in Live-Music [Music Style] [Event Time] [Price]

MenuItem: App: Moz Sticks [Price]

MenuItem: App: Chicken Tenders [Price]

MenuItem: Meal: Burger & Fries [Price]

MenuItem: Meal: Mac & Cheese [Price]

MenuItem: Beverage: Soft Drink: Cola: Pepsi [Price]

MenuItem: Beverage: Hard Drink: Rum: Cpt Morgans [Price]

If I selected Mac's BBQ and Advanced, I'd get a list like this

Dine-in [Empty] [Empty] [Empty]

Live-Music [Music Style] [Event Time] [Price]

Mechanical Bull [Empty] [Empty] [Price]

MenuItem: App: Moz Sticks [Empty] [Empty] [Price]

MenuItem: App: Chicken Tenders [Empty] [Empty] [Price]

MenuItem: Meal: Burger & Fries [Empty] [Empty] [Price]

MenuItem: Meal: Mac & Cheese [Empty] [Empty] [Price]

MenuItem: Beverage: Soft Drink: Cola: Pepsi [Empty] [Empty] [Price]

MenuItem: Beverage: Hard Drink: Rum: Cpt Morgans [Empty] [Empty] [Price] and more Menu Items

So my question is: How? How can I choose two different parameters and generate a list of items that fit that? I've got a master list of all the possible list items, with a column for what Restaurant it's from and a column for whether this item is Simple or Advanced. I tried vlookup and hlookup for this, and Pivot Tables didn't seem to help in this application. I was able to learn how to make a drop down dependent on the value of another drop down which made it easy to remove Advanced from Restaurants that lack that option. But I'm just stuck now. I feel like it's obvious and I'm just not seeing it.

I asked a related question a few days ago and was recommended Pivot Tables. This is for the same spreadsheet but a different portion, so I've made it a different question. I think I've conveyed what I'm trying to do, if you need more info let me know. I created an example, though it lacks any formulae- it ~appears~ right-ish.. C:\fakepath\Pricing Formula Test2.ods

edit retag flag offensive close merge delete