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.

2015 super bowl squares spreadsheet template

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.

2015 super bowl squares printable excel

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

Download the spreadsheet for free, simply click the link below which will take you to the, where the file is hosted. Next, click on the “Download” link in the upper right hand corner.That’s it!

2015 Super Bowl Squares.xlsm download

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.

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

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
'active the pool manager spreadsheet
'select location where the copied column will be inserted
'insert the new column and shift all others to the right
Selection.Insert Shift:=xlToRight
' Close customer workbook
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.