Thursday, September 24, 2009

NFL Weekly Pool Template

The other day I got a request for a simple template for a weekly NFL office pool. After a few more clarifying questions, here's the scenario:

The requester is the organizer for a weekly NFL football office pool. Each person picks the winner of each of the 16 games each week during the regular season -- no scores, no picking against "the line", etc. -- just a straight up pick, and for each correct pick, that person gets a point. The person who has the most correct picks at the end of the week wins. In the event of a tie, each person also predicts the total combined points scored in the Monday night game. Whomever is closest wins.

I whipped together a quick template that can either be printed and used manually or filled out in Excel as actual results come in, automatically calculated scores and tie-breakers, if necessary. I thought it was decent, so I thought I'd share it -- with everyone, not just Excel_Geek Insiders (though, Insiders, I'll be emailing your copy to you personally, and everyone else has to download it on their own.)



Wednesday, September 16, 2009

Mail Merge and Named Ranges

Okay, so today I was helping out a friend over at Husker Diesel who's pulling together a customers/contacts list into Excel. One of his planned uses is for printing mailing/shipping labels. "No problem," i thought. "Let's just crank up a good old-fashioned mail merge in Word.

Turns out it was pretty easy, but i ran across some knowledge along the way i thought I'd share:

For a variety of reasons that are immaterial to the story, the Excel contacts list we were working with wasn't just a simple top-row-contains-headers type of list. There's a form at the top of the page and the column headers started further down the page -- say row 11 or so. Sooo...when i browsed for the Excel file in the mail merge process what it pulled back wasn't very useful -- the form part of the worksheet was included in the contact records since it's at the top. "No problem," i thought. "Let's just create a dynamic named range that will define just the contact records part of the sheet .

Turns out that mail merge doesn't recognize dynamic named ranges. ?! Odd.

I couldn't find a lot out there on this issue, so I ended up just using the work around of created a "hard defined" named range of 10,000 rows of the contacts list, just to be sure there was ample room for all the contacts he'd be adding. Doing this caused me to have to select only non-blank rows during the mail merge process, but it worked just fine.

If anyone out there has encountered this and has a better work around, I'd love to hear it.