+ Reply to Thread
Results 1 to 13 of 13

Multiple Data validation in Drop Down Lists

  1. #1
    Registered User
    Join Date
    03-08-2016
    Location
    Portugal
    MS-Off Ver
    2013
    Posts
    7

    Multiple Data validation in Drop Down Lists

    Hi everyone,

    Have been struggling for a while to get a document correctly formatted but with no success. Would very much appreciate all the help you can give me.


    Ref Data.
    - 1 column "X" with the values: A, B, C, D, E, F
    - 1 column "ABC" with some values
    - 1 column "DEF" with some values
    - 1 column "123" with some values
    - 1 column "456" with some values


    Sheet:
    - "X" field on A2 as drop down list
    - Drop down list field on A3 with ref data from "ABC" and "DEF"
    - "X" field is also on A4 cell as a drop down list
    - Drop down list field on A5 with ref data from "123" and "456"


    Problem:
    - In Cell A2 if the user chooses A, B or C the drop down list in A3 should only retrieve values from column "ABC" and if the user chooses D, E or F the drop down list in A3 should only retrieve values from column "DEF".
    In A3 cell the field has to be populated depending on the option choosen in A2 cell.

    - In Cell A3 if the user chooses A, B or C the drop down list in A4 should only retrieve values from column "123" and if the user chooses D, E or F the drop down list in A3 should only retrieve values from column "456".
    In A3 cell the field has to be populated depending on the option choosen in A3 cell.


    Have been trying with INDIRECT, VLOOKUP ando some other random hints found online but none matches my expectations and problem.
    Can you guys give me a hand please?

    Thank you in advance

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,299

    Re: Multiple Data validation in Drop Down Lists

    Please post a sample file not image) showing expected results.

  3. #3
    Registered User
    Join Date
    03-08-2016
    Location
    Portugal
    MS-Off Ver
    2013
    Posts
    7

    Re: Multiple Data validation in Drop Down Lists

    Hi John,

    Thank you for your reply.
    Not sure how to post a sample file without formatting the sheet as I referred, trying to do a sample I believe it will be more confusing.

    But maybe with the image it becomes a little more clearer what I'm trying to achieve.

    Capture.JPG

    Sheet:
    - "X" field on A2 as drop down list
    - Drop down list field on A3 with ref data from "ABC" and "DEF"
    - "X" field is also on A4 cell as a drop down list
    - Drop down list field on A5 with ref data from "123" and "456"


    Problem:
    - In Cell A2 if the user chooses A, B or C the drop down list in A3 should only retrieve values from column "ABC" and if the user chooses D, E or F the drop down list in A3 should only retrieve values from column "DEF".
    In A3 cell the field has to be populated depending on the option choosen in A2 cell.

    - In Cell A3 if the user chooses A, B or C the drop down list in A4 should only retrieve values from column "123" and if the user chooses D, E or F the drop down list in A3 should only retrieve values from column "456".
    In A3 cell the field has to be populated depending on the option choosen in A3 cell.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,299

    Re: Multiple Data validation in Drop Down Lists

    PLEASE post a file:

    Click "Go Advanced" then "Manage Attachments" to upload file.

  5. #5
    Registered User
    Join Date
    03-08-2016
    Location
    Portugal
    MS-Off Ver
    2013
    Posts
    7

    Re: Multiple Data validation in Drop Down Lists

    Here it goes.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,299

    Re: Multiple Data validation in Drop Down Lists

    If I have understood your requirement

    Created Named Ranges: ABC_1, DEF_1, ABC_2, DEF_2 corresponding to your column headings 1,2,3 and 4

    For DV

    in J4

    =IF(AND(CODE($I4)<=67),ABC_1,DEF_1)

    in L4

    =IF(AND(CODE($K4)<=67),ABC_2,DEF_2)

    Copy down
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-08-2016
    Location
    Portugal
    MS-Off Ver
    2013
    Posts
    7

    Re: Multiple Data validation in Drop Down Lists

    Master John,
    Thank you very much, this will do the trick perfectly.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,299

    Re: Multiple Data validation in Drop Down Lists

    If you are happy with the result, can you please mark the thread as solved ("Thread Tools" at top of first post).

  9. #9
    Registered User
    Join Date
    03-08-2016
    Location
    Portugal
    MS-Off Ver
    2013
    Posts
    7

    Re: Multiple Data validation in Drop Down Lists

    Done!
    Thank you once again.

  10. #10
    Registered User
    Join Date
    03-08-2016
    Location
    Portugal
    MS-Off Ver
    2013
    Posts
    7

    Re: Multiple Data validation in Drop Down Lists

    Hi guys,

    The solution provided works like a charm, however, it would be great if it would be possible to escalate this formula to a more complex system.
    The goal is instead of having 2 possible reference data columns stated here before as ABC_1 and DEF_1 have double that so imagine "GHI_1" and "JKL_1".

    I understand the logic to be applied but when it comes to formulate that get's a little tricker because using the CODE formula I cannot just state less than X and greater than Y. For this I'll probably need to define the code range with the use of OR functions I believe, but already searched online for some help in order to do it myself but it's becoming a quest.

    Attach send an excel already adapted based on John Topley example, only thing missing is the correct formula.

    Any chances you guys can give me a hand?

    Thank you in advance
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,299

    Re: Multiple Data validation in Drop Down Lists

    Set the columns (Column1, Column2 as named ranges)

    Then set DV for Column11 as

    =IF(CODE($M4)<=67,Column1,IF(CODE($M4)<=70,Column2,IF(CODE($M4)<=74,Column3,IF(CODE($M4)=75,Column4,Column5))))

    Repeat the above for Column 12 changing named ranges to Column6 to Column10

  12. #12
    Registered User
    Join Date
    03-08-2016
    Location
    Portugal
    MS-Off Ver
    2013
    Posts
    7

    Re: Multiple Data validation in Drop Down Lists

    That's great John, thank you.

    Still encountered a few issues with the fact of having more than one reference value starting with the same ASCI char code but resolved it by changing one to CAPS.

    Much appreciated

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,299

    Re: Multiple Data validation in Drop Down Lists

    Alternative


    Named range DV_LISTS: 3 columns

    DV_LISTS
    A Column1 Column6
    B Column1 Column6
    C Column1 Column6
    D Column2 Column7
    E Column2 Column7
    F Column2 Column7
    G Column3 Column8
    H Column3 Column8
    I Column3 Column8
    J Column3 Column8
    K Column4 Column9
    L Column5 Column10

    DV for Column 11

    =INDIRECT(VLOOKUP($M4,DV_LISTS,2,0))

    DV for Column 12

    =INDIRECT(VLOOKUP($M4,DV_LISTS,3,0))

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] data validation lists - drop down lists too small
    By reglook0736 in forum Excel General
    Replies: 3
    Last Post: 04-24-2015, 12:49 PM
  2. [SOLVED] Activate a filter using multiple Data Validation drop down lists
    By BeachRock in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2013, 12:09 AM
  3. [SOLVED] Data Validation using multiple dependent drop down lists
    By ceruppel in forum Excel General
    Replies: 4
    Last Post: 12-17-2012, 06:39 PM
  4. [SOLVED] Widen drop down validation lists for multiple columns
    By aaanenson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-11-2012, 10:07 AM
  5. [SOLVED] drop down lists / data validation
    By CashmereCat in forum Excel General
    Replies: 3
    Last Post: 10-11-2012, 12:35 AM
  6. Drop down based on multiple validation lists
    By projectile in forum Excel General
    Replies: 9
    Last Post: 11-17-2011, 11:11 PM
  7. Data Validation - Drop Down Lists
    By Guzziguy in forum Excel General
    Replies: 2
    Last Post: 06-04-2009, 03:24 PM

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