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



Later,

Excel_Geek

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.

Later,

Excel_Geek

Google Analytics Data in Excel

Have you seen this? Very cool. (Thanks for finding this, Bart.) I haven't dug into it yet, but plan to soon...

Stay tuned.

Chat Support RE: Custom Number Formats

Here's a short one from today:

[15:39] meeboguest######: hi, Im trying to import 11 digit UPC numbers into my software, but can't find the correct format to keep the digits at 11. All my UPC numbers that start with a 0 are cut off and made 10 digits every time I try to import
[15:39] meeboguest######: I tryed formatting the columns into text and it still shows 10 digits if it start with 0.
[16:15] excel_geek: go to Format --> Cells...
[16:15] excel_geek: from the Number tab select Custom
[16:16] excel_geek: then replace where it says "General" with 11 zeros.
[16:16] excel_geek: tada!



Gotta love customer number formats.

$50 Project - Parse Google Search Results within a Domain for First Result

I recently completed a $50 Project for a client who wanted a spreadsheet where he could have a list of domains in which to search and a list of search terms and return the URL for the first returned search result from Google while searching withing the specific domain. It took a little modification of some other stuff I've done with parsing Google search pages, but it seems to work nicely.

Excel_Geek Insiders, your file is on it's way.

Later,

Excel_Geek

Chat Support RE: Counting in Pivot Tables

Here's another one from yesterday:

[15:40] meeboguest######: hi excel_geek - looking for a cont function in a pivot table... can you help?
[15:40] excel_geek: cont?
[15:40] excel_geek: count?
[15:40] meeboguest######: oups.. "count"
[15:42] meeboguest######: have multiple entries by various "agents" and wnat to find how many agents are ther in total... regardless of the entries
[15:42] excel_geek: sure
[15:42] excel_geek: right click in the table detail part
[15:42] excel_geek: and pick field options
[15:42] excel_geek: then select "count"
[15:44] excel_geek: follow?
[15:44] meeboguest######: ok
[15:46] meeboguest######: it gives me the total count of entries, not count of unique individuals... (1 individual = multiple entries)...
[15:47] excel_geek: hmmm
[15:47] excel_geek: maybe i should back up
[15:48] excel_geek: so drag the agents field into the left part of the pivot table
[15:48] excel_geek: then drag any other column into the right (detail) part
[15:48] excel_geek: the right click the field you put in the right part and field settings to "count"
[15:50] meeboguest######: ok... i think i figured it out!
[15:50] meeboguest######: thx a bunch :)
[15:50] excel_geek: np
[15:50] excel_geek: thanks for stopping by
[15:50] meeboguest######: :D

10 Minutes to understanding. Not too bad. Some of that pivot table stuff is unintuitive.

Chat Support RE: =INDIRECT() Function

More and more I'm helping folks via the MeeboMe chat window I've embedded in the blog. I thought it'd be interesting for you all to see the sorts of conversations I'm having this way:

[14:36] meeboguest######: eric!
[14:36] meeboguest######: do you have a sec?
[14:36] XLgeke: for u?
[14:36] XLgeke: always
[14:36] XLgeke: and now it's over... ;)
[14:36] XLgeke: what's up
[14:36] meeboguest######: excel question
[14:36] meeboguest######: umm
[14:36] meeboguest######: trying to think how to explain
[14:37] XLgeke: reboot
[14:37] XLgeke: that should do it
[14:37] meeboguest######:
[14:37] meeboguest######: ok, I'm wondering if there's a way to dynamically reference a cell in a formula
[14:37] XLgeke: yes
[14:38] meeboguest######: so that if I was to reference cell B
[14:38] XLgeke: right
[14:38] XLgeke: use =INDIRECT()
[14:38] meeboguest######: and that number is a value generated in another cell
[14:38] XLgeke: so like
[14:38] meeboguest######: oooh
[14:38] XLgeke: =INDIRECT("B"&B1")
[14:38] meeboguest######: woah
[14:39] meeboguest######: nice
[14:39] XLgeke: in B1 you'd change the number for BX
[14:39] meeboguest######: right
[14:39] meeboguest######: I'll give that a shot
[14:39] meeboguest######: thanks!
[14:39] XLgeke: np

And that was it...less than 3 minutes and issue solved. This happens scores of times each month.