## Sunday, January 18, 2015

### 2015 Super Bowl Squares

The NFL playoffs are over and the final two teams are set to go at it in the championship game, meaning it’s time to download the 2015 Super Bowl Squares template! The Seattle Seahawks will play the New England Patriots in Super Bowl XLIX (49) on Sunday, February 1st at 6:30pm. To make the big game even more fun start an office pool using our printable Super Bowl square spreadsheet. Fill in the squares, watch the game, laugh at the commercials, and see who wins and maybe make a little money at the same time. If you’re a fan of a team that like mine (the Factory of Sadness) that will never make it to the championship (or playoffs for that matter), then playing Super Bowl Squares will make watching the game more exciting and gives you something to actually root for. Others may be torn between cheering for their favorite team and cheering for their squares.

This year’s Super Bowl Squares spreadsheet includes multiple versions - you pick the way you want to play:
1. Printable version - This print ready version contains a blank grid, simply print it off and write all the player’s names down on the piece of paper. It’s quick and easy!
2. Electronic version - Manage everything directly in Excel. Press the randomize button to generate the random score numbers. Use this for running your Super Bowl pool. No modifications are necessary, though you can if you so desire.

Super Bowl Squares Rules & How to Play
Listed below are the instructions on how to play Super Bowl Squares (which are also included within the spreadsheet, along with an example):
1. Participants “purchase” individuals squares by writing their name in their desired squares, until all one hundred are filled.
2. After all squares are taken, the numbers 0 through 9 are written in the empty horizontal and vertical rows in random order (draw the numbers out of a hat).
3. After the end of each quarter of the game, match the last digit of each team's score to the corresponding square to find the winner. Tip: 0, 3, and 7 are the best numbers to get. 8, not so much.

Below is a short video tutorial I put together of how to use my Super Bowl Squares template in Excel

Let me know in the comments or by email which version you’ll use: paper or electronic!

NEW: 2016 Super Bowl Squares Here

## Wednesday, January 7, 2015

### How to import data from one Excel spreadsheet to another using VBA

It can be very helpful knowing how to import data from one Excel spreadsheet to another using VBA. In fact, one of the new features I added to my College Football Bowl Prediction spreadsheet is the ability to import a user’s picks from a separate entry form into the pool manager’s tracking sheet automatically. I did this by inserting a button in the manager’s file that is linked to a VBA macro. If you're brand new to programming macros in Excel, please see my getting started guide here.

The import spreadsheet VBA code is below. The comments colored green explain what is going on. This code is pretty quick and dirty - no real error handling has been inserted to deal with unexpected situations, something that should be added.

``` 'this code imports a single player's picks into the pool manager 'player's picks must be saved as .xls file '============================================================== Sub Import_Single_Player_Data() ' Get player's workbook... Dim customerBook As Workbook Dim filter As String Dim caption As String Dim customerFilename As String Dim customerWorkbook As Workbook Dim targetWorkbook As Workbook ' make weak assumption that active workbook is the target Set targetWorkbook = Application.ActiveWorkbook ' get the customer workbook filter = " *.xls,*.xls" caption = "Please Select an input file " customerFilename = Application.GetOpenFilename(filter, , caption) Set customerWorkbook = Application.Workbooks.Open(customerFilename) 'define the target sheets in order to copy data from customer to target workbook Dim targetSheet As Worksheet Set targetSheet = targetWorkbook.Worksheets(2) Dim sourceSheet As Worksheet Set sourceSheet = customerWorkbook.Worksheets(1) 'select and copy column L from the new player's spreadsheet sourceSheet.Columns("L:L").Select Selection.Copy 'active the pool manager spreadsheet targetSheet.Activate 'select location where the copied column will be inserted Columns("L:L").Select 'insert the new column and shift all others to the right Selection.Insert Shift:=xlToRight   ' Close customer workbook customerWorkbook.Close End Sub ```

See how this import spreadsheet macro works by downloading the bowl pool manager template. As I’ve stated earlier, using my completed templates is a great way to figure out how to do things that can be applied to other tasks.

By the way, I copied the code into this blog post with formatting by using Notepad++, one of the programming tools I use everyday.