Data Validation List Issues/Sorting Issues

**taylorbe** · 03-11-2014, 01:40 PM

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.

**FDibbins** · 03-11-2014, 04:14 PM

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))

**taylorbe** · 03-12-2014, 09:53 AM

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.