+ Reply to Thread
Results 1 to 9 of 9

Adaptive dropdown lists (Google sheets)

  1. #1
    Registered User
    Join Date
    12-30-2018
    Location
    Worcestershire, UK
    MS-Off Ver
    O365
    Posts
    4

    Adaptive dropdown lists (Google sheets)

    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.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    24,301

    Re: Adaptive dropdown lists (Google sheets)

    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.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

    Forum Rules Updated September 2018. Please read them by clicking here.

  3. #3
    Registered User
    Join Date
    12-30-2018
    Location
    Worcestershire, UK
    MS-Off Ver
    O365
    Posts
    4

    Re: Adaptive dropdown lists (Google sheets)

    Okay, I'm just working on anonymising it and making some examples.
    Last edited by AliGW; 12-30-2018 at 12:39 PM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,675

    Re: Adaptive dropdown lists (Google sheets)

    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    STOP PRESS: Forum Rules Updated September 2018! Please read them here.
    Are you new to Power Query and don't know what to do with the code you've been given? Have a look here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    12-30-2018
    Location
    Worcestershire, UK
    MS-Off Ver
    O365
    Posts
    4

    Re: Adaptive dropdown lists (Google sheets)

    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
    Last edited by pessimist101; 12-30-2018 at 01:40 PM. Reason: Idiocy

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    23,675

    Re: Adaptive dropdown lists (Google sheets)

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

  7. #7
    Registered User
    Join Date
    12-30-2018
    Location
    Worcestershire, UK
    MS-Off Ver
    O365
    Posts
    4

    Re: Adaptive dropdown lists (Google sheets)

    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?
    Attached Files Attached Files
    Last edited by pessimist101; 12-30-2018 at 01:44 PM. Reason: Expanded explanation

  8. #8
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    20,567

    Re: Adaptive dropdown lists (Google sheets)

    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!!!
    Attached Files Attached Files
    Glenn



  9. #9
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    24,301

    Re: Adaptive dropdown lists (Google sheets)

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1