Friday, August 11, 2017

NEW THIS YEAR! College Football Pick 'Em Pool!

Hey everyone! I recently had a person approach me to convert my NFL Pick 'Em Pool into a college football pick 'em pool. What a great idea! It was no easy feat, however, since for the college football pool, I had to allow the pool administrator to pick the games each week that he or she wanted to include in the pool, whereas for the NFL pool, the schedule is the schedule. Also, for this particular person, I needed to support the way they handled the bowl season. The result is pretty cool, though.


Here's how the pool works: at the beginning of the season the pool administrator determines the games that will be involved each week -- up to a maximum of 16 games each week -- and sends out the picks sheets for the entire season. All participants make their picks for the entire season at the beginning. They also can optionally have people answer some tie-breaker questions, though no scoring for these is accounted for in the master spreadsheet. Then the participants send their picks sheets in.

I wrote a macro that enables the pool admin to automatically pull in the picks from the participants' sheets, so that they won't have to manually key them in. They simply need to save the unprocessed picks sheets into a user-definable directory and click a button. Once they're processed, the pool administrator can move those picks files to a "processed" directory. What a time saver if you've got a couple hundred participants, right? I'll have ot figure out if I can do something similar for the NFL Pick 'Em...

I also adapted the automatic score updating from FootballDB.com, so as long as you've picked games tracked by that website, you can use that feature. 

Because the system is based off of the old NFL Pick 'Em pool file, you'll still have the choice to go straight picks vs. picking against the spread, and the choice of how to allocate points for correct picks. 


I can already anticipate, since my readers truly are the nicest, most appreciative people on the planet, that some of you will be offering to pay me or donate to me for the sheet. This, like the college bowl pool and NFL pick 'em sheets I do, I do for free -- because I enjoy them. That said, if you just _have to_ do something for me for my efforts, feel free to PayPal me whatever you like. It does help to cover my hosting costs, etc.:

$5.00     $10.00     $25.00     $50.00

Really you just have to change that dollar amount in the URL -- https://paypal.me/excelgeek/50.

Anyways...please enjoy this inaugural NCAA Football Pick 'Em pool. We're bound to run into a bug or snag or learning curve or two along the way, so when you do, just let me know, and I'll do my best to help resolve any issues.

Later,

Excel_Geek

5 comments:

keylimesoda said...

I'm kind of a rookie with Excel, can you help me understand the workflow here?

1. Generate a copy of the entry form for each player
2. Place each form in my skydrive folder
3. Send a "sharing" link to each player for their input form
4. "Process" the inputs on the pool form

Q: How do I link the schedule in my pool sheet with what shows up on the input forms? Should the input forms be auto populated/updated when I update the schedule on the pool sheet?

Excel_Geek said...

@Keylimemsoda... The way the system was designed (per direction from the customer who requested and paid for it) was that the entire schedule would be determined at the begin ning of the season. So it's not really designed to determine the game schedule each week. That's something I may be trying to figure out for next year. Also, I've done no testing of the macros when the files are stored in a Skydrive environment, so I'm not sure how well or if that will work.

chris moseley said...

are you going to do the college bowls spreadsheet / workbook again? That thing was awesome to use last year!!

Excel_Geek said...

@Chris, of course!

chris moseley said...

Sweet! Can't wait.