+ Reply to Thread
Results 1 to 7 of 7

Multiple dependent lists with unique results

  1. #1
    Registered User
    Join Date
    02-24-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    16

    Multiple dependent lists with unique results

    So that title is most probably misleading at best, entirely wrong at worst. I have 13 named columns that are used to generate one list. Based on the selection on that list, I want a drop-down list to the right to be populated with unique results from the previous list. My end goal is to use three drop down lists to generate a key for the spreadsheet. I've included the current spreadsheet for reference. But, an example would be that the user selects "County" on the first drop down. The second drop down is then populated with all the unique values under the column "County". Lastly, the third drop down list would be a stagnant list of colors. When the user selects county, county name (whatever county that happens to be), and then the color red, that all rows featuring that county are highlighted red. I'm not the most advanced user, and am pulling my hair out. Any help would be appreciated.MortgageBackUp2.xls

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Multiple dependent lists with unique results

    Jeepster325,

    Welcome to the forum!
    Attached is a modified version of your posted workbook.
    I renamed Sheet2 to be named 'Lists'
    In 'Lists' cell A1 is this formula:
    Please Login or Register  to view this content.

    In 'Lists' cell B2 is the header Colors
    In column B it contains three colors to be used for your "stagnant list of colors": Red, Yellow, and Green


    I then made several new custom named ranges:
    StartCell is a named range defined with this formula:
    Please Login or Register  to view this content.

    EndCell is a named range defined with this formula:
    Please Login or Register  to view this content.

    rngData is a named range defined with this formula:
    Please Login or Register  to view this content.

    BlankCell is a named range defined with this formula:
    Please Login or Register  to view this content.

    List2 is a named range defined with this formula:
    Please Login or Register  to view this content.

    ListColors is a named range defined with this formula:
    Please Login or Register  to view this content.


    With all of those named ranges defined, I was able to create this formula to go into sheet 'Lists' cell A2 and copied down to cell A21 (for a maximum of 20 unique results. You can copy it down more if necessary):
    Please Login or Register  to view this content.


    On sheet 'Sheet1' cell I4 is this data validation formula:
    Please Login or Register  to view this content.

    On sheet 'Sheet1' cell I5 is this data validation formula:
    Please Login or Register  to view this content.


    Lastly, I used conditional formatting to highlight the appropriate rows the specified color. This is done with three conditional format rules with the following formulas:
    To highlight red:
    Please Login or Register  to view this content.

    To highlight yellow:
    Please Login or Register  to view this content.

    To highlight green:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Registered User
    Join Date
    02-24-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Multiple dependent lists with unique results

    That is amazing. It works entirely as you formatted it. However, because this function is going to double as a literal KEY for the spreadsheet, where your drop down lists occupy I4 and I5, they would need to go to J3 and K3, respectively. I relocated the drop downs, and changed the formula's to reflect the move. This worked to duplicate how your lists populate, but when i replaced I4 and I5 in the conditional formatting formula's with J3 and K3, it stops working. Based off of what you wrote, I thought that should be a minor change and work fine... no such luck. Suggestions?

    PS, i attached the revised sheet.

    MortgageBackUp4.xls

  4. #4
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Multiple dependent lists with unique results

    Jeepster325,

    The problem is due to the merged cells of K3:L3. Unmerge that (generally speaking, try to never have merged cells), and then update the conditional formatting formulas.
    Here is an updated conditional format formula for the green highlighting:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    02-24-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Multiple dependent lists with unique results

    I unmerged as you suggested.
    Your original was:
    =AND(INDEX($B5:$M5,0,MATCH($I$3,$B$11:$M$11,0))=$I$4,$I$5="Green")
    After unmerging I tried:
    =AND(INDEX($B1:$M1,0,MATCH($I$3,$B$11:$M$11,0))=$J$3,$K$3="Green")
    =AND(INDEX($B5:$M5,0,MATCH($I$3,$B$11:$M$11,0))=$J$3,$K$3="Green")
    =AND(INDEX($B3:$M3,0,MATCH($I$3,$B$11:$M$11,0))=$J$3,$K$3="Green")

    No such luck on any of them ;o/

  6. #6
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Multiple dependent lists with unique results

    Jeepster325,

    See attached, I have corrected the conditional formatting. Your issue was that you probably weren't applying the conditional format to the whole range B:M, but to a specific cell or row. See here for information on conditional formatting: http://www.contextures.com/xlCondFormat01.html
    Last edited by tigeravatar; 03-05-2013 at 11:40 AM. Reason: Had to re-upload attachment

  7. #7
    Registered User
    Join Date
    02-24-2013
    Location
    Washington
    MS-Off Ver
    Excel 2010
    Posts
    16

    Re: Multiple dependent lists with unique results

    That did it. I'll go over the info from the link. You are a life saver, thank you very much for the time.

+ 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