Wednesday, August 10, 2016

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.

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:

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...

@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.

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?

@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.

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?

@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.

@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.

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.

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)

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

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

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,

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.

@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.

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).

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.