+ Reply to Thread
Results 1 to 3 of 3

Data Validation List Issues/Sorting Issues

  1. #1
    Registered User
    Join Date
    03-11-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Angry Data Validation List Issues/Sorting Issues

    Hello All,

    I am having some issues with using data validation. My workbook contains multiple sheets with tables. One sheet draws the information from all of these tables into a consolidated location. From there, I have formulas to remove duplicate entries from a list of dates, formulas to take that no-duplicate list and remove blank cells from it, and that leads to my first question. The column containing an unsorted, no-duplicate, no-blanks list needs to be sorted and then used in data validation. The issue here is that I do not know how to take this list with formulas, extract the date information, and sort it properly. I have tried advanced filters to no avail (I am not well-phrased in VBA, so any assistance there would be appreciated).

    My second question regards data validation. I am trying to use VLOOKUP to interface data validation lists, and have the second validation list create a list illustrating all information pertinent to the first data validation list choice.

    I apologize if this sounds extremely convoluted, and I have provided an workbook illustrating my issues.

    In Cell B1 of the "Primary" sheet, there is a data validation drawing from sheet "ClassList" under the "NoBlanksNoDupes" heading. I need this data validation to draw from a separate list, as the "NoBlanksNoDupes" list is not sorted and I do not know how to get it to that point.

    Once you select a date in Cell B1 of "Primary", it prompts you to select a class in cell B2. This is where I run into issues with my data validation. I need the data validation to use VLOOKUP to find the date in !Primary B1 in the list on sheet "ClassList" (!ClassList C2:G23), and be able to reference additional information from there. At this point, my primary problem is this data validation keeps prompting me "The Source currently evaluates to an error. Do you want to continue?", and I have no idea where to go from there.

    Any and all help would be appreciated in this matter, and I look forward to hearing back from you all because I am about reading to slam my head in a drawer.

    Thanks.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    Re: Data Validation List Issues/Sorting Issues

    Hi and welcome to the forum

    Instead of the formula you have in Classlist A2, try this...
    =IFERROR(INDEX($C$2:$C$23,MATCH(0,INDEX(COUNTIF($J$1:J1,$C$2:$C$23),0,0),0)),"")
    You can then use this to sort...
    =LARGE($A$2:$A$10,ROW(A1))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    03-11-2014
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: Data Validation List Issues/Sorting Issues

    FDibbins,

    Thank you for the welcome! Found myself cast into a job here recently that involves a lot of Excel -- so I will probably be pretty regular on the forum. Thank you for the suggestion on the sorting; that worked perfectly! Any ideas about the data validation? I still don't know why it's giving me such hassle.

+ 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 ISSUES!!!
    By Will03 in forum Excel General
    Replies: 10
    Last Post: 10-27-2013, 07:15 AM
  2. [SOLVED] Macro in Validation List - Code Issues
    By Pony08 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-31-2013, 11:03 AM
  3. [SOLVED] Competition Template...No Spaces in Validation List needed.... manual calculation issues.
    By skyping in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-29-2013, 06:52 PM
  4. Help with Data Validation issues
    By mal.b.graham in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-16-2012, 04:52 AM
  5. issues with a VLOOKUP and Data Validation
    By long_Kieran in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-01-2011, 01:22 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