Wednesday, August 10, 2016

2016-17 NFL Pick 'Em Pool

Folks, I've completed and release this season's NFL Pick 'Em pool. Like past years, many of you have diligently been pestering me for weeks now. Keep doing that! It's paying off. I'm actually getting this out well before the season, unlike last year. Now, I didn't have the ambitious list of new features this year that I did last year, but I did have to contend with the Rams moving back to LA, which actually ended up being more involved to change than I'd like to admit. Also, at the request of several pool admins, I did create 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.

UPDATE: I guess since I was able to get this year's version out so early compared to years past, I've been compelled to throw in one last feature enhancement. Many of you through the years have been asking for an automatic game results/score updater, using data from the web. I've got that feature now implemented, pulling scores from my score information website of choice -- footballdb.com. Please let me know if you encounter any issues using this new feature. That is all.

Without further ado, here are the files:

Also as 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. This, like the college bowl pool sheet I do, I do for free. That said, if you are so compelled, 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

43 comments:

Austin Hall said...

Is this for the confidence pools as well?

Excel_Geek said...

@Austin, yes. There's an option to use a confidence point system, rather than all games being worth 1, if that's what you mean. Download and check it out.

Mel Irvin said...

Great sheet - I am hoping it is not a problem to unprotect the sheet and delete any not needed participant rows to make printing out each weeks results easier.

Excel_Geek said...

@Mel: That should not be a problem at all.

Anonymous said...

Links do not work for me.

Excel_Geek said...

@Anonymous-Links-don't-work:

Keep trying? They seem to work for everyone else. You can also email me -- ehunzeker [at] gmail -- and I'll just send you the files if you like.

JJ said...

I'd like to set this up so that ties (against the spread) count as 0.5 points instead of 0 points. Can you point me in the right direction to updating the formulas so that it works this way. I suppose I could dig into it, but if I was willing to do that I probably wouldn't be looking on line for a pre-done spreadsheet...

Thanks for doing this!

Excel_Geek said...

@JJ, that wouldn't be a super easy change to make, but how you'd go about making it is to unprotect each week's sheet, then unhide the row/column headings. To the right of the picks/points table, you'll see that the columns between AN and CZ are hidden. Unhide those to reveal the cells where the points calculations are done. You'll see a fairly long, nasty nested IF THEN statement in there. I just looked at it briefly, so I couldn't tell you exactly what to do to it right off, but it's certainly possible to make the change you're after. Be sure to rehide and reprotect once you're done.

Shawn Christenson said...

Hi, I'm using openoffice.org and I downloaded your files and I can see them ok and everything, however when I click on the weekly tabs the matchups do not show up. Do I need to do something to make this happen or is it because I'm using the free version of excel?

Excel_Geek said...

@Shawn, it probably is because the spreadsheet application in Open Office has very different support for conditional formatting, and that's what's used in my spreadsheet in the picks area. If you like, i can create a version that doesn't include conditional formatting and you can try that. Email me -- ehunzeker at gmail com.

Shawn Christenson said...

I decided to actually buy excel because you are right openoffice does not allow all the stuff that you have done on this spreadsheet. I downloaded excel and damn you have done a lot of work to this, lol. Anyways thanks for taking the time to do so, I would think next year all I would have to do is change the schedule then right?

Excel_Geek said...

@Shawn, yeah, getting Excel is definitely the easier approach. Regarding next year, you can try to modify this one yourself OOOOoooorrrr you can wait for me to release next year's sheet, maybe with even more cool features.

Marco said...

Can you save this to google sheets and it still work? Also can you delete the unused rows without messing the sheet up.

Mel Irvin said...

How hard would it be to allow .5 point spreads - we make every game a half point one way or another so there are no ties.

Anonymous said...

Never mind - figured it out!

Excel_Geek said...

@Marco: This will not work well (or maybe at all) as a Google sheet. You can delete as many unused rows as you like and it should not impact operation.

Anonymous said...

whats the easiest method to share the game schedule with friends every week?

Excel_Geek said...

@anonymous RE: sharing game schedule. Download the "picks" version of the file and send that to everyone. They can select which week they'd like to see and it dynamically updates that week's schedule. They can make their picks in that file, save and send it to you to copy and paste special values (always paste special values) into the master file you maintain for the appropriate week.

Jamie Adams said...

How can I add more then 10 Participant Thanks this is Great

Excel_Geek said...

@Jamie RE: adding 10 more participants...

It's not super straightforward, but here's what you'd do: First up, unprotect the weekly sheet for Week 1. Next display the row/column headings to make things easier. Then select the entire row 309 by clicking the row heading, copy it, and paste it below that row as many times as you need more participants. Rename/number those new participants as you like. Next you'll need to update the formulas in the WEEKLY PTS RANK and TIE-BREAK RANK columns to include those new rows. Re-hide the column/row headings and re-protect the sheet. Repeat this for all of the weekly sheets. Next, unprotect the entire workbook, then unhide a sheet called cumulative_points. You'll need to add in new corresponding rows here, too, being sure to insert them above row 304 in that sheet, and alter all of the formulas in columns C through W to include those newly added rows. Then rehide that sheet, reprotect the worksbook, and you should be golden. Let me know if you get hung up.

Yoda Coster said...

Can you please tell me where the spreads pull from? Or if there is a way to enter them manually each week?

Kyt McGee said...

Hello, I tried to update weekly scores and got the following error. It's the last line that was highlighted in yellow as the error. Please advise. Thanks


Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = "http://www.footballdb.com/scores/index.html?lg=NFL&yr=" & THIS_SEASON & "&type=" & THIS_SEGMENT & "&wk=" & THIS_WEEK
objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")

Excel_Geek said...

@Yoda Coster RE: pulling spreads...

I'm not automatically pulling the spreads from anywhere. If you're using spreads, that'll be up to you to input those. You can get them from any number of sites, as I'm sure you're aware.

Excel_Geek said...

@Kyt McGee RE: error...

It'd probably be easier to troubleshoot if you can send me the file than it will be trying to do so over blog post comments. ehunzeker [at] gmail.

Steve Hudson said...

Trying to get ir to auto update week 1 scores. What ma I missing?

Steve Hudson said...

I found the problem. Its a visual basic not authorized. I'm using micro crap office for mac.

Rapture1469 said...

When trying to update score I get an error:

THIS_SEASON = Range("THIS_SEASON").Value

THIS_SEASON is highlighted in blue and the sub title is yellow

Compile Error: Can't find project or library.

Rapture1469 said...

There seems to be an issue if you sort week 1 it sorts all the other weeks names but not their picks. When I sort week 1 it applies the sort to the names in the other sheets, but if there are picks there, they stay in the same rows. I will see if I can find a solution to this.

I added another button to sort alphabetically on each sheet so entering picks is easier. (this also allowed me to manually get the names back to their picks on week 2)

Rapture1469 said...

I figured out the first issue (obviously) - I had to uncheck the Microsoft 16 Object Library (I think that was it).

I really like how you can get the scores! Thanks!

Mel Irvin said...

Hi again. I have 21 players so I deleted all of the rows below that in week 1. Week 1 went great. When I tabbed to week 2 all of the rows I deleted came up with a #ref error. No problem as I just set a print area to ignore those rows. This morning when I finished entering in scores the weekly sort worked great, but when I did the season sort it moved the 21 rows with data in to the bottom of the sheet, and sorted with all of the #ref cells at the top. If it easier I can e-mail you my sheet.
Sincerely...Mel

Excel_Geek said...

@Mel RE: deleting rows... Why don't you just delete the corresponding rows in all of the weekly tabs? Should work fine then.

Mel Irvin said...

OK - I thought I had read somewhere earlier that all of the weeks were ties to what was entered in week 1. Just went in and unprotected the sheet and deleted the unused ones in week 2 and it sorted correctly.
Thx-Mel

Anonymous said...

Hello, in our pool we do a quarterly prize for every 4 weeks. Is there any way to add this column into the totals portion of this sheet? How hard would that be to set up?
Would need it to add up the points for weeks 1-4, 5-8, 9-12. and 13-16. Thanks,

Excel_Geek said...

@Anonymous RE: quarterly prizes...

Feel free to unprotect the workbook and worksheets -- nothing is password protected -- and give it a try.

Anonymous said...

When I Delete the columns for Tie Break Points, it seems to throw off the rest of the totals. This is where i tried to add the quarterly totals, but once i deleted the Tie Break, the totals for the season did not add up.

Excel_Geek said...

@Anonymous RE: deleting Tie Break Points column...

Is there any particular reason to delete that column? I'd leave it. Hide it if you like, but leave it in place. Insert other columns for what you want.

Steven Hanson said...

Is it possible to add more Persons?

Steven Hanson said...

In regards to using the automatic updating scores, does it matter how we format the team names into the Visitor and Home sections?

Excel_Geek said...

@Steve Hanson, yes, it's possible to add more participants, though it's not super easy. You'll have to unprotect the workbook, unhide some other tabs, make a bunch of mods, etc. With regards to updating scores, I have no idea what you man by "format the team names". You don't really get to format anything. Depending upon the weekly tab you're on, the team names are there for you. You don't edit them.

Anonymous said...

I'm using this format for college football games and would like to add 4 more columns for our weekly 20 game pool play. Can you help??

Excel_Geek said...

@Anonymous RE: adding four more games to use for college pool...

It's not super easy to do, but I've left the sheet open (it's protected but not with a password) for anyone to mod how he or she sees fit. If you know your way around Excel, you'll be able to see what will be necessary to make those changes. I try not to directly help every person who wants to make their own little mod, since I'd forever be making little tweaks, but if enough folks wanted to use this for college pools, I might decide to make the official Excel_Geek college football pool spreadsheet for next season. My advice, dive into it and see what you can do. If you get stuck, ask me questions. I can guide you along, perhaps. I just don't want to take it on myself (yet).

Anonymous said...

i have taken out the score column and tie break pos. column because i don't use them. Did not think this would effect total wins column. When i add new column and add new column to formula in total win column, the cell under new column stays red and does not compute to total win column.

Anonymous said...

I believe I got it. Thx for your help and this is a great pick em sheet.