Dynamic Validation From Database

MYSQL has a SELECT UNIQUE option, doesn't it? Couldn't you use that to bring back the unique values from a specific field in a specific table?

Once those unique values appear in your destination column, a dynamic named range using OFFSET() on that column will present those values in data validation based on that named range.

Unfortunately doing a select Unique will not provide the solution I need, because I still need to return the entire data set. The reason for the drop-down menu is to filter the result on a "Report" page in excel. So the user will be able to, in Excel show only results matching the selected option from their drop-down, but the entire result set will be in the workbook.

Also, the OFFSET () is not one that I am familiar with although I have seen it. Could you perhaps explain this formula to me and what its used for?

Yes, SELECT UNIQUE should give you the results you want. I'm not suggesting you bring back less data in your current connection, I'm recommending you set up a second connection that bring back just the unique values into a second sheet.

Anyway, now that I've heard your answer an even simpler is evident. You can turn on the AutoFilter on the data set you've imported and your users can use the drop down in any column, including the one you're currently considering, and filter by any single value, or even multiple values. Very powerful, handy, builtin, and already includes the drop down you're after.

Yes, I am merely referring to the standard Autofilter. Again your further questions underline its features as desirable as you can use the autofilters multiple filtering options in multiple columns. "Nice" of course is a relative thing, for me "nice" is not having to recreate the wheels already existing in Excel.

I don't see any problems with the formula posted, what are your expectations of it?

The formula I posted is unfortunately only working on the last two options I select.

Selecting only the very first option as an example doesn't give me any results as all.

And also, regular filtering will not work here, as this is not the way in which the people want to view the report. The need counts of various criteria.

It's hard to be specific with no sample to look at but I'd suggest you use advanced filter to copy the subset of data you want to a separate worksheet and point all your count formulas at that sheet, or use a pivot table and slicers if you have 2010. As Jerry said, "nice" isn't much of a guide to what you want.

You could use a formula like this in E9 (and copied across):

=COUNTIFS(data_source[Division],IF($C$3="-- Division --","*",$C$3),data_source[Cost Centre],IF($G$3="-- Cost Centre --","<>0",$G$3),data_source[Manager],IF($K$3="-- Manager --","*",$K$3),data_source[Job Description],IF($O$3="-- Job Description --","*",$O$3),data_source[Employment Type],IF($S$3="-- Employment Type--","*",$S$3),data_source[Gender],IF($C$4="-- Gender --","*",$C$4),data_source[Race],IF($G$4="-- Race --","*",$G$4),data_source[Age Group],IF($K$4="-- Age Group --","*",$K$4),data_source[Education Level],IF($O$4="-- Education Level --","*",$O$4),data_source[Service Years],IF($S$4="-- Service Years --","*",$S$4),data_source[Category 1 Q1.1],E$8)

Note that because Cost Centre is numeric, you have to use "<>0" rather than "*" for all records. You'll also need to add in the filtering for S1 and S2.

Also note that the * wildcard will only match rows that have some data in the relevant column, so if you may have blank cells in some of the filter columns, that would be a problem with this approach.

If the actual data can be numeric or text, I'd use the <>0 criteria.

Perfect, thanks.
This is my final solution I have applied and it is working like a charm

This is the E9 formula. Thanks Again!!!


=COUNTIFS(data_source[Period],IF($S$1="-- Period --","<>",$S$1),data_source[Month],IF($S$2="-- Month --","<>",$S$2),data_source[Division],IF($C$3="-- Division --","*",$C$3),data_source[Cost Centre],IF($G$3="-- Cost Centre --","<>0",$G$3),data_source[Manager],IF($K$3="-- Manager --","*",$K$3),data_source[Job Description],IF($O$3="-- Job Description --","*",$O$3),data_source[Employment Type],IF($S$3="-- Employment Type --","*",$S$3),data_source[Gender],IF($C$4="-- Gender --","*",$C$4),data_source[Race],IF($G$4="-- Race --","*",$G$4),data_source[Age Group],IF($K$4="-- Age Group --","*",$K$4),data_source[Education Level],IF($O$4="-- Education Level --","*",$O$4),data_source[Service Years],IF($S$4="-- Service Years --","*",$S$4),data_source[Category 1 Q1.1],E$8).

I originally wanted to use a countifs formula, but didn't know / think about using the Wildcard (i.e the criteria of each criteria range)