Adaptive dropdown lists (Google sheets)

[pessimist101]

I've got several groups of products in their own tables with prices. I have a separate table with dropdown lists, using data validation. I have it set up so that you can select the products from the dropdown lists and it automatically updates the prices in the column next to it. I want to make it so that you can only select certain combinations of products. Here is what I want it to do:

There are 3 different product types, we'll call them TYPE1, TYPE2 and TYPE3. A user must select one product of each type. Each product is of a different price tier, we'll call them TIER1, TIER2, TIER3, TIER4 and TIER5. If a user selects product TYPE1 of TIER1, they cannot select product TYPE2 of a tier any higher than TIER2. I hope this makes sense.

I also have access to Office365, as I understand there may be feature limitations in google sheets, but I have made this in google sheets and, if possible, would like to see it through in google sheets.

[Richard Buttrey]

It's not clear to me exactly what you mean.

Please upload the google sheets document, anonymised if necessary. It is always easier to advise if we can see your request in its context.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

[pessimist101]

Okay, I'm just working on anonymising it and making some examples.

[AliGW]

When you have done that, please attach it here (you will not be able to post a link to it).

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[pessimist101]

I can't attach the link to my google spreadsheet

EDIT: I was stupid and didn't reload the page so I didn't see the very clear instructions on what to do

[AliGW]

Attach the workbook itself. As a new member, you can't post a link. Instructions in the post above yours.

[pessimist101]

I've attached my example spreadsheet

I want to make it so that when I select product 1, the product 2 selection changes to only show items from specific tiers. In this case,
if product1tier=1 then product2tier allowed=1,2
if product1tier=2 then product2tier allowed=3
if product1tier=3 then product2tier allowed=4,5

maybe another vlookup formula?

[Glenn Kennedy]

I am not convinced that this is correct, as your description in your post and in your sheet didn't seem to tally with each other.

take a look. IF it is what you want, I'll explain it. If it's not please explain what you want... Don't explain non-working formulae, explain the logic behind what you do want.

A final warning: I have no idea if it will work on Google Sheets!!!

[Richard Buttrey]

Can I suggest you pay a visit to Debra's Contextures site here

https://www.contextures.com/xlDataVal02.html

Where you'll learn all you need to know about dependent drop down lists and more.