+ Reply to Thread
Results 1 to 3 of 3

Data validation filtered list based on a criteria

  1. #1
    Registered User
    Join Date
    09-03-2014
    Location
    Norway
    MS-Off Ver
    Office 2010
    Posts
    1

    Data validation filtered list based on a criteria

    Hi guys,
    I'm struggling with a little case.
    On sheet 1 I have a list of attributes based on a code:
    24-04-2015 15-36-28.jpg

    On my second sheets I have a column with type and a column for attribute.
    24-04-2015 15-37-53.jpg

    What i want to do sounds simple but I haven't found a solution.
    If name/type is "cable" I want a dropdown list for "att" allowing only code1 values.
    If name/type is "htrc" I want a dropdown list for "att" allowing only code2 values.

    What I've tried is:
    - defining a named range called "codes" for $A:$A on sheet1
    - data validation for B2 on sheet2 and used formula: =IF($A2="cable";INDIRECT(codes="code1");INDIRECT(codes="code2"))

    When I'm entering that I'm getting a message saying "The Source currently evaluates to an error. Do you want to continue?" and the dropdown is blank...

    Anyone can help?
    I'm also attaching the file.

    testbook.xlsx

    I hope someone can help me...

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Data validation filtered list based on a criteria

    1) To make this dynamic, you need a second named range on your Sheet1. In column C:D create a lookup table where you can assign names (column C) to codes (column D)
    Please Login or Register  to view this content.
    Now highlight C:D and give those two columns a named range value of "KeyNames". Now you can add as many options to this list as you wish.

    2) The data validation formula in Sheet2 B2 is now:

    =OFFSET(codes, MATCH(VLOOKUP(A2, KeyNames, 2, 0), codes, 0)-1, 1, COUNTIF(codes, VLOOKUP(A2, KeyNames, 2, 0)), 1)
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Expert
    Join Date
    09-20-2011
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    2,278

    Re: Data validation filtered list based on a criteria

    Hello
    Highlight the code1 values and Name them 'cable', likewise, highlight the code2 values and Name them 'htrc'. Then for the Data Validation List source, use the function:

    Please Login or Register  to view this content.
    *Just noticed Jerry's post after I posted. If you have many codes it's the way to go.

    Hope this helps.
    DBY
    Last edited by DBY; 04-24-2015 at 11:05 AM. Reason: Added note*

+ 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. Data Validation List based on criteria
    By dluhut in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-27-2014, 05:28 PM
  2. [SOLVED] Create Dynamic Data Validation List based upon Criteria
    By stubbsj in forum Excel General
    Replies: 6
    Last Post: 06-04-2012, 08:33 AM
  3. Replies: 3
    Last Post: 08-21-2011, 08:22 PM
  4. Filtered Validation List based on other variables
    By t1mmclaren in forum Excel General
    Replies: 1
    Last Post: 05-27-2009, 12:35 AM
  5. data validation list should have opt. to select based on criteria
    By be in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-15-2005, 09:05 AM

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