+ Reply to Thread
Results 1 to 8 of 8

Question about how to create a drop-down list conditional on the cell to the left

  1. #1
    Registered User
    Join Date
    09-13-2018
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 16.16.1
    Posts
    7

    Question about how to create a drop-down list conditional on the cell to the left

    Hi all! Thanks for the reply yesterday, so helpful.

    I've got my quoting spreadsheet close to being finished but want to add a few features to make it easier to use.

    I've got a section that will quote metal costs. In cell B10 I've got a drop down menu with a few choices, Square Tube, Rectangular Tube, etc.

    In cell C10, I would like a drop down list that is conditional on the result in B10. So if B10 contains Square Tube, C10 accesses the drop down list that contains sizes of Square Tube. If B10 says Flat Bar, C10 accesses the drop down list that contains sizes of Flat Bar.

    How can I do this?

    Thanks again!

  2. #2
    Valued Forum Contributor
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    883

    Re: Question about how to create a drop-down list conditional on the cell to the left

    Think you could be similar to the below

    https://www.excelforum.com/excel-for...drop-down.html

  3. #3
    Registered User
    Join Date
    09-13-2018
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 16.16.1
    Posts
    7

    Re: Question about how to create a drop-down list conditional on the cell to the left

    Not sure how this works. I'm in the beginning process of learning all this. I'll examine what's in the link. Downloaded the file but need to know more on this.

  4. #4
    Valued Forum Contributor
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    883

    Re: Question about how to create a drop-down list conditional on the cell to the left

    You create a first dropdown, which you have done

    the value in cell b10 can be matched against lists for each of your tube types. the lists are on sheet 2
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    09-13-2018
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 16.16.1
    Posts
    7

    Post Re: Question about how to create a drop-down list conditional on the cell to the left

    This is what I would like to do. Thanks again for your patience with me.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-13-2018
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 16.16.1
    Posts
    7

    Re: Question about how to create a drop-down list conditional on the cell to the left

    Thanks davsth! I see it works.; I'm going to try to learn the context of the data validation formula! Another lesson to learn!

  7. #7
    Registered User
    Join Date
    09-13-2018
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 16.16.1
    Posts
    7

    Re: Question about how to create a drop-down list conditional on the cell to the left

    I'm trying to understand the context of the formula, =OFFSET(Sheet2!B2,0,MATCH(B10,Sheet2!C1:E1,0),10,1). I don't understand the reason for B2, is that the offset? I've looked for info on the OFFSET formula and can see "reference", "rows", "cols", but can't grasp how this formula fits into that format. I put in this formula in my spreadsheet, in DATA VALIDATION-LISTS:

    =OFFSET(DROP-DOWN LISTS!B2,0,MATCH(B10,DROP-DOWN LISTS!C1:E1,0),10,1) but it asks me if I want to create a formula.

    Attaching and hoping that if I can get help with this referencing the actual cells in my actual spreadsheet, I'll understand better. My apologies for my ignorance, trying to teach myself with a lot of help from my friends!

    Thanks!
    Attached Files Attached Files
    Last edited by SyzygyMoi; 09-16-2018 at 03:25 PM.

  8. #8
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO200;Win10/MSO2016
    Posts
    9,893

    Re: Question about how to create a drop-down list conditional on the cell to the left

    You need named ranges for each table on the dropdown lists sheet:
    RECTANGULAR_TUBE ='DROP DOWN LISTS'!$K$2:$K$13
    ROUND_TUBE ='DROP DOWN LISTS'!$G$2:$G$27
    SQUARE_TUBE ='DROP DOWN LISTS'!$E$2:$E$18
    THICKNESS ='DROP DOWN LISTS'!$I$2:$I$7
    TubeTypes ='DROP DOWN LISTS'!$M$4:$M$9


    on sheet one the data validation formula for description is: =tubetypes
    for size it will be:
    Please Login or Register  to view this content.
    because you will have to replace the space in the description with an underscore since spaces are not allowed in range names

    then for thickness, just =thickness
    Attached Files Attached Files
    Ben Van Johnson

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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