Data Validation Quirk When Using Named Ranges

So here's a goofy item I stumbled upon today. (Others of you, I realize, have known about this for some time.)

Here's the situation:

1. You are using Data Validation to restrict input for a particular cell to items from a list (Data --} Data Validation --} List).
2. You have the "Ignore Blanks" option checked.
3. The list you are referencing is a Named Range.
4. The list you are referencing contains one or more blank cells.

What happens, you ask? Well, it lets users input any value into the cell that's supposed to be data validated without warning. Nice, right?

Three simple options to fix:

1. Uncheck the "Ignore Blanks" option.
2. Don't have any blanks in your Named Range.
3. Don't use a Named Range.

Just thought I'd pass it along...

Later,

Excel_Geek

Bowl Season Wrap-Up

Well, another college bowl season is in the books. For those of you keeping score, Mrs. Excel_Geek beat Excel_Geek for the third straight year (or so). And in a nail-biter, new-comer Excel_Geek Jr. matched his daddy for the number of correct picks, but was narrowly edged out by 8 confidence points. So you know, I picked for the little guy, using the =RANDBETWEEN() function and the =RAND() function -- maybe I'll do a post describing how to do that -- to make his picks completely randomly. That's right. My own, well-thought-out, analyzed, expert picks did only very slightly better than completely random picks and point assignments. Oh well...back to the drawing board. See you all next bowl season!

Later,

Excel_Geek

Oh, almost forgot some traffic stats: the 2011-12 Bowl Season (12/3/11 - 1/9/12) increased ExcelGeek.com website traffic by nearly 3.9x the average daily site traffic for the year leading up to it, whereas the 2010-11 Bowl Season (12/4/10 - 1/10/11) increased traffic by (only) nearly 3.4x. 2011-12's peak 20-day moving average traffic day (12/23/11) was nearly 44% higher than that from 2010-11 (12/24/10). The single day peak from 2011-21 (12/5/11) was 72.8% higher than the single day peak in 2010-11 (12/6/10). Overall...a great year.

Next up...March Madness!

Problem with Individual Charts in College Pool Corrected

Oops!
Zach let me know that he found a problem with the Individual Charts tab in the 2011-2012_Bowl_Pool_Summary file. Participants 21-40 were't working properly - showing "no picks made" even if valid picks were input into the Picks tab.

I've fixed this. If you download it now, it'll work properly.

Sorry about that!

Eric

Expanded (Again) Bowl Pool Spreadsheet

It's done. At least I think it all still works. Now you have room for up to 100 participants. I also added in 5 extra bowl game rows for future expansion if necessary. For those of you with large pools, this outa work just fine. Again, you can thank JMorein and EGeierman for footing the bill.

Also, again, this new file is only compatible with Excel 2007 or 2010, as it's far wider (in columns) than is supported in Excel 2003 and older.

click here to download the file.

Enjoy!

Excel_Geek

Another expansion of the College Bowl Pool Spreadsheet is afoot!

I caved (again). I'll be expanding the current College Bowl Pool Spreadsheet to accommodate up to 100 participants. There could be some other minor changes as well. Thank JMorein and EGeierman for spearheading the effort (and footing the $50 bill I'm enforcing to do this). If any others would like to contribute to the $50, simply email me (you should be able to find my email address on this website somewhere) or send me your email using the little chat thingy indicating that you'd like to contribute, and I'll send you out the new files once they're done.

UPDATE: This expanded version will definitely only be for those on Excel 2007 & 2010, given the width in columns it'll take.

2011-12 College Football Bowl Pool Spreadsheet

It seems like the first time in a few years (not sure if this is true, but it feels like it) that I don't have to modify the spreadsheets to add another game. "Just" 35 bowls again this year.

The Humanitarian Bowl has been replaced by the Famous Idaho Potato Bowl. The Texas Bowl is now called the Meineke Car Care of Texas Bowl. The old Meineke Car Care Bowl has been replaced by the Belk Bowl. Yeah...Belk. I had to look it up too. "Charlotte, N.C.-based Belk, Inc. (www.belk.com) is the nation’s largest privately owned mainline department store company with 305 Belk stores located in 16 Southern states. The company was founded in 1888 by William Henry Belk in Monroe, N.C., and is in the third generation of Belk family leadership. The belk.com Web site offers a wide assortment of fashion apparel, shoes and accessories for the entire family along with top name cosmetics, a wedding registry and a large selection of quality merchandise for the home." Okay. Anyway...the reason I even bring any of this up is that while I know that the sponsors of these bowl games spend a lot of money to get their brands out there, where convenient, I'm just going to leave the sponsor name off in the spreadsheet -- e.g. i'll just put "Music City Bowl" instead of "Franklin American Mortgage Music City Bowl". In cases where the sponsor name IS the bowl name (e.g. "Champs Sports Bowl"), I'll obviously just use that. The new "Famous Idaho Potato Bowl" i'll just call the "Potato Bowl" in the whole "Orange Bowl", "Cotton Bowl", etc., tradition.

Now that that's out of the way, here are the files:

Click here for the "Master" file used by the pool organizer. This is the one with the fancy charts and group scoring summary, etc.

Click here for the "Individual Picks" file used by each person participating in the pool. This is the one the pool organizer typically would email out to his or her participants to have them fill out to make it easier for him or her to copy paste their picks into the "Master" file.

Enjoy, everyone!

Excel_Geek

I've already gotten a slew of inquiries as to when this year's college football bowl pool spreadsheet will be ready. Patience, Grasshopper(s). Once all of the bowls have selected their teams, it will be ready.

Not familiar with the spreadsheets? Click the "College Bowl Game Pool" tag to see what I've done in the past.

Later,

Excel_Geek