Wednesday, August 25, 2021

2021-22 NFL Pick 'Em Pool Files Are Ready!

Folks, I've completed and released this season's NFL Pick 'Em pool. Like past years, many of you have been diligently pestering me for a few weeks now. The tweaks I had to make to this year's version from last year's were to 1) update to the 2021-2022 schedule, which adds a Week 18 for the first time ever, and 2) make one small change to the macro that automatically pulls scores from FootballDB.com, as they must have just slightly altered their HTML code on the scores pages. I did test that change using prior years and all appears to be working, but, as always, just let me know if things don't seem to be working.

Like years past, there's also a companion "Picks" sheet to distribute to pool participants, so they can simply email back the file for the admins to copy-paste into the master sheet. Just remember to always copy, paste special... values when moving their picks into the master sheet, so as to not jack up formatting or other stuff.

Without further ado, here are the files:

As also happens each year, several folks, as they were pinging, pestering, checking-in with me offered to pay me or donate to me for the sheet. I remind them that this, like the college bowl pool sheet I do, I do for free. That said, if you just _have to_ do something for me for my efforts, feel free to PayPal me whatever you like:

$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 year's NFL Pick 'Em. If you encounter a bug or need help with using the sheet, let me know.

Later,

Excel_Geek

22 comments:

Unknown said...

Thanks so much for doing this!

Did you remember to take out the code for the preseason games so that it will pull the data from the regular season games?

Excel_Geek said...

@Unknown RE: remembering to take out the testing code from preseason games...

Yep. It should be all set. As always, though, let me know if it doesn't seem to be working once you actually try pulling scores from week 1.

Unknown said...

So having this saved to my desktop, it should automatically pull the scores? Or is there something special i need to do. This is my first time using this.

Excel_Geek said...

You'll need to click Enable Macros when you initially open the file, and there's a button on each week's sheet you click to pull scores. Let me know if you have any trouble.

Edgar said...

Thanks for the spreadsheet!!

Is there a way to export each weekly spreadsheet to send out to participants?

Excel_Geek said...

@Edgar RE: exporting each weekly spreadsheet...

I'm not sure why this is needed. The "picks" version of the sheet is the sheet intended to be used by the participants. If you click in the cell that contains "Week 1" you'll see it's a picklist wherein you/they can select the week and the sheet updates. The idea is that prior to the first game of each week, the participants select that week, make their picks and send them into the organizer. Is this what you need?

Edgar said...

Thanks for the reply I see it now!! Sorry!

Excel_Geek said...

@Edgar... no worries, man. Have fun!

Mr. Football said...

I cannot download the link. it just flashes and does not open.

Excel_Geek said...

@Mr. Football RE: download link. Try another browser or if you use Chrome try an ingognito browser.

Unknown said...

On the favorite row, font color of drop down box and spread value isn't visible against background...any ideas?

Excel_Geek said...

@Unknown RE: favorite row formatting... Did you go to the Settings sheet and choose to use point spreads?

Unknown said...

This is Awesome! I cant afford much but Ill def send you somehting your way. One thing though, the link to run the scores is not working I get this error...
"Run-time error ' -2147024891 (800700005)':
Access is denied."

Anyway you can help me? If not, its tottaly okay, I am just appreciative of the help this provided.

Excel_Geek said...

@Unknown RE: access denied error...

I'll reach out via email (thanks for the the PayPal -- I assume that was you) to help.

Unknown said...

Good afternoon, I'm trying to open the link, however, when I click it nothing happens? Am I doing something wrong?

Thanks!

Excel_Geek said...

@Unknown RE: nothing happening when you click the link...

Try a different browser or try an Incognito tab in Chrome.

KSolis23 said...

Thanks for these sheets, they're awesome! I continue to get a run time error 429. Is this because I am running on a MacBook Pro and not a Windows device?

Excel_Geek said...

@KSolis23 RE: Run time error 429 and using a Mac...

Yep. Your results may vary. I don't use a Mac or test code on a Mac (or using an emulator). Sorry I won't be much help.

KSolis23 said...

I appreciate your response! By chance, would you know a quick way to import a google forms answer into these picks? Anyways, I appreciate these sheets you do. Thanks!

Excel_Geek said...

@KSolis23 RE: importing picks from Google form...

I've never tried to do any sort of automation on this front, but it would seem to be pretty simple if you merely wanted to be able to copy-paste responses from a Google form (which will exist in a Google Sheet) into my spreadsheet. The only issue is if you're using confidence point or not because of how my sheet is laid out -- picked winner then confidence points. If you're using confidence points, you could simply set up your Google Form to be "who'll win the game between X and Y?" followed by "and how many points do you want to wager on that pick?" Then the responses will be laid out -- front left to right -- the same way my sheets are laid out, and you'd be able to select all of the responses and copy-paste (always paste special... values!) into my sheet. Oh, I guess there would be the issue of the top to bottom order of participant responses. You might have to copy paste one row and a time and paste into the right row in my sheet. Anyway... interesting idea. Good luck!

Unknown said...

Any chance I can get a copy of the file? Doesn't allow me to download from the link. Had to take over office pool this week and would love to automate it

Excel_Geek said...

@Unknown RE: getting the file...

Try right-clicking and opening in an Incognito window.