Wednesday, September 4, 2019

2019 College Football Schedule Spreadsheet in Excel

The 2019 college football season is here! Sorry, I had some trouble with the macro I setup to automatically help in the creation of this spreadsheet so it took longer than expected to finish.

This spreadsheet of the college football schedules includes every team from all conferences plus independents. Every game is listed as either home, away, or neutral site (noted at the bottom of each sheet). 

One of the differences in this year's sheet is I opted to use the team's logos from ESPN rather than their helmets, since I was missing quite a few of them. Let me know if you are OK with this change or not.

For the third year in a row I’ve used macro automation to automatically create this sheet - no more manual copying and pasting for hours with many mistakes. The schedules are imported directly from ESPN (so if there is a mistake blame them!). 

2019 college football schedule download


Download the CFB schedule spreadsheet here. To get it for free, enter a 2 into the "name a fair price" box, click "I want this!" then on the next page enter discount code "2019" and it will drop the cost to zero dollars. I had to do it this way due to the size of the file and the limits placed on my Gumroad account.

2019 CFB Schedule Spreadsheet

As always, I welcome any comments or suggestions about how to fix or improve the sheet! Let me know if and how you’re using the sheet.

Wednesday, August 28, 2019

2019 NFL Helmet Schedule Spreadsheet

Sorry this took a bit longer than I was expecting it to but now available to download is the 2019 NFL helmet schedule spreadsheet. You’ll see a comprehensive breakdown of every NFL pro football team's 2019 season schedule with an image of each team’s helmet design. 


2019 nfl helmet schedule spreadsheet in excel football


Use this link to download the NFL schedule spreadsheet. Please note, an email is required to download it. I do this so you will be automatically updated you if changes or additions are made and will update you when the next year’s schedule is ready. I do not use your email for anything else.

Here's a video overview of last year's sheet to show you what it looks like:



As you can see, the NFL helmet schedule is printable too. You can save the spreadsheet as a PDF file or print it out and pin it up in your cubicle at work. If you do, please email or tweet me a picture of it hanging up - I'd love to see it!

As always, I welcome any comments or suggestions about how to fix or improve the sheet! How can I improve this football spreadsheet into something you’ll use all the time during pro-football season? What future features would you like to see?

Monday, March 25, 2019

How to hide checkboxes and rows in Excel with VBA

I recently received a question about how to hide or collapse a row if a checkbox in that row wasn’t checked. There are a lot of good lessons in this example. When you’re stuck on a problem, the best thing to do is to break it down into smaller problems and solve each one at a time. Let's do it!


How to Insert Checkboxes in Excel


The first lesson is how to add a checkbox to an Excel sheet. Go to Developer tab then Insert. There are two types of checkboxes: Form Control and ActiveX Control. For this example, we are going to use Form Control checkboxes because you can link these directly to a cell.




How to link a checkbox to a cell


The next lesson is how to assign a checkbox to a cell. We’re going to do this to help with hiding the rows later. When you create the first Form Control checkbox in cell A2, in the formula bar type “=$B$2” to assign B2 to the checkbox. Now, if the checkbox is checked B2 should read TRUE, if not selected it should say FALSE.

**The one downside to this method is if you need 100 checkboxes it could take some time to manually assign each checkbox to a cell. There is probably a way to automate this task, however, for simplicity of this example we’re going to say we only need five checkboxes and assign them all manually.**

Now your example sheet should look like this:



How to hide rows based on a cell’s value in VBA

The hiding of all the checkboxes and rows will be done automatically with a VBA macro. If you’re new to macros see this gettingstarted guide. 

Again, break it down into smaller problems. First, let’s figure out how to hide rows based on a condition, the TRUE or FALSE value in our helper column.

Define the first row that contains data we might want to hide:
BeginRow = 2

Define the last row that might contain data we want to hide.
EndRow = 5

Define the column number of our helper info, the column with the true or false values.
ChkCol = 2

Loop through the range of rows we just defined and if the value of the cell in our helper column is false, then hide that entire row:

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = False Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt

How to hide checkboxes automatically with macro

Dim CB As Shape
Dim sh As Worksheet

Set sh = ActiveSheet

We need to loop through all the shapes in the active sheet, see if they are the form control type of Check Box:

For Each CB In sh.Shapes
  If CB.Type = msoFormControl Then
    If CB.FormControlType = xlCheckBox Then
      'MsgBox CB.Name, vbOKOnly
     
      'if check box is checked, keep it visible, otherwise, hide it
      If CB.OLEFormat.Object.Value = 1 Then
        CB.OLEFormat.Object.Visible = True
        Else
        CB.OLEFormat.Object.Visible = False
End If
     
    End If
  End If
Next CB

Full Macro to Hide Rows and Checkboxes

Here’s the final code that hides entire rows based on checkbox is checked or not.

Sub Hide_checkboxes()

Dim CB As Shape
Dim sh As Worksheet

Set sh = ActiveSheet
For Each CB In sh.Shapes
  If CB.Type = msoFormControl Then
    If CB.FormControlType = xlCheckBox Then
      'MsgBox CB.Name, vbOKOnly
     
      'if check box is checked, keep it visible, otherwise, hide it
      If CB.OLEFormat.Object.Value = 1 Then
        CB.OLEFormat.Object.Visible = True
        Else
        CB.OLEFormat.Object.Visible = False
End If
     
    End If
  End If
Next CB

'now hide the rows
    BeginRow = 2
    EndRow = 5
    ChkCol = 2

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = False Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt


End Sub

How to unhide rows and checkboxes in Excel

Conversely, I also made a code to reset everything and unhide all checkboxes and unhide all rows:

Sub Unhide_checkboxes()

Dim CB As Shape
Dim sh As Worksheet

Set sh = ActiveSheet
For Each CB In sh.Shapes
  If CB.Type = msoFormControl Then
    If CB.FormControlType = xlCheckBox Then
      'MsgBox CB.Name, vbOKOnly
     
      'if check box is checked, keep it visible, otherwise, hide it
      If CB.OLEFormat.Object.Value = 1 Then
        CB.OLEFormat.Object.Visible = True
        Else
        CB.OLEFormat.Object.Visible = True
End If
     
    End If
  End If
Next CB

'now hide the rows
    BeginRow = 1
    EndRow = 5
    ChkCol = 2

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = False Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt
   
End Sub

How to insert a button and link to a macro

Finally, for ease of use we can add two buttons to our sheet to run each one of the macros. Go to Developer tab, insert command button.



Watch the video below to see how the macro to hide checkboxes and rows works.



As you can see, there is much to glean just from this one example.

Tuesday, February 12, 2019

How to send email from Excel with link back to workbook

One of my most popular articles is where I showed how to create a hyperlink in an Excel spreadsheet that when clicked composes an email message. This method used only hyperlink and concatenate formulas; no VBA macros. It’s simple, easy to use, and not too complicated. But you can only do so much with this method.

Today, I will be using the more complicated method of using VBA macros to show how you can add a button to your spreadsheet that when pressed will automatically compose an email containing a link to the file location of the spreadsheet. I’m constantly sharing spreadsheets with friends of coworkers. I don’t know how many times a day I create a new email message, copy and paste a file location into the message, create the hyperlink to the spreadsheet, etc. This macro will help save you some time (and hopefully impress your coworkers in the process).


Setup Your Spreadsheet to be Able to Send Emails From Excel

If you’re going to send the email to the exact same people every time, you could just include the email addresses inside the vba code itself. But if the people change or someone who doesn’t know VBA needs to update it, this is not the best solution. What I like to do is add a helper sheet, and on this sheet list all the email addresses of who to include in the “TO” line and who to “CC.” This way anyone with no coding knowledge can easily change who the email is for.


VBA Macro to Compose Email with Hyperlink

Now it’s time to write the Excel macro. I’ll walk you through it step by step.

Sub Compose_Email()

I almost always add these to optimize macro speed:

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

Dim Wb1 As Workbook
Set Wb1 = ThisWorkbook

Declare variable names for the cells where the email addresses will be saved:

Dim SendTo1 As String
Dim SendTo2 As String
Dim cc1 As String
Dim cc2 As String

The email address are entered on SendList sheet, my “helper” sheet. As I said earlier, you can change email address without having to change VBA code because the macro refers to the cell’s location as opposed to entering the email address itself:

Wb1.Sheets("SendList").Select
SendTo1 = Range("B1").Value
SendTo2 = Range("C1").Value
cc1 = Range("B2").Value
cc2 = Range("C2").Value

'back to the first sheet
ThisWorkbook.Sheets(1).Select

Now get the name shown in the upper right hand corner of Excel to use as the signature:

Dim OwnerName As String
OwnerName = Application.UserName

Get the workbook name. We’ll use this as our email subject line:

Dim WorkbookName As String
WorkbookName = Wb1.Name




Get the location where the spreadsheet is saved:

Dim FileLoc As String
FileLoc = Wb1.FullName

Time to start Microsoft Outlook if it hasn't already been started:

Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String

On Error Resume Next
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)

The code will be slightly different for non-html email versus html email. For now, I am only going to show you how to do html email so we can easily link back to the spreadsheet. This is the body of the email. <br> is html code to go to the next line. Chr(34) is for quotation marks. This will make more sense once you run the macro and see the end result.

xMailBody = "Dear Team, <br><br>" & "I've updated the weekly financial report. Please check and sign this:" & "<br><br>" & _
"<a href=" & Chr(34) & FileLoc & Chr(34) & " > " & WorkbookName & " </a> " _
& "<br><br>" & "Thanks," & "<br><br>" & OwnerName

Fill in each section of the newly created email message:

On Error Resume Next

With xOutMail
.To = SendTo1 & "; " & SendTo2
.CC = cc1 & "; " & cc2
.BCC = ""
.Subject = WorkbookName
'.Body = xMailBody
.HTMLBody = xMailBody
.Display 'or use .Send

End With

On Error GoTo 0

Set xOutMail = Nothing
Set xOutApp = Nothing

Reset Macro Optimization Settings:

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub


Add a Button to Run the Macro


Finally, now that the code is done you can add a button to your spreadsheet by going to the Developer tab, Insert, Button (Form Control). Link your newly created macro to the button, customize the button text, and that’s all there is to it!



Watch how the macro works in the video below where I also walk you through it step by step:


I’ve made what I’ve shown into a simple template you can download here:

Compose Email with Link Example Spreadsheet.xlsm download here

I hope you find this example will make sharing spreadsheets with your coworkers easier. Let me know what you think about this template in the comments below.

Monday, January 28, 2019

How to find the values of diagonal cells in a table in Excel

Today I'm going to show you how to use the OFFSET function combined with Index and Match to find the values of diagonal cells in a table using my Super Bowl squares template as example.

Use the OFFSET function to find the value of a cell to the upper left, lower left, upper right, or bottom right of another cell. Let's say my name is in cell B2, the reference cell. To go up a row use -1, to go down a row use 1. Use -1 to go to a column to the left, +1 to go to a column to the right.

Upper left: =OFFSET(B2,-1,-1)
Upper right: =OFFSET(B2,-1,1)
Lower left: =OFFSET(B2, 1,-1)
Lower right: =OFFSET(B2, 1,1)

Now this is useful if you know the exact location of the reference cell, but what if that reference cell is always moving? That's where INDEX and MATCH come into play. A perfect example is taken from my Super Bowl Squares spreadsheet template. Watch the video below to see how I can get the values of the diagonal cells when the reference cell can change once I hit the randomize numbers button:



What do you think of that? Would you like to see more examples like this, taken straight out of a template? Let me know in the comments below.

Monday, January 21, 2019

Super Bowl Squares Template 2019 - SuperBowl LIII Grid Game

The match-up for the Super Bowl is set which means it's time to download your Super Bowl Squares Template 2019 edition. Surprise, not surprise: Tom Brady and the New England Patriots (man, I don't even have to update this year to year) will play the Los Angeles Rams in Super Bowl 53 on Sunday, February 3rdat 6:30pm. To make the big game more interesting you can start a football office pool using our printable Super Bowl 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 (or help raise money for charity). If you’re a fan of a team like mine (the Factory of Sadness Hope) that will never make it to the championship (or even get a taste of the playoffs), 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.


superbowl grid game print

This year’s Super Bowl Squares spreadsheet includes three different versions so you can choose 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. Pick your own squares - Manage the office Super Bowl pool directly in Excel. In this version, each player gets to pick what squares they want. Once all are taken, press the randomize button to generate the random score numbers. No modifications are necessary, though you can if you so desire.
  3. Assign squares randomly - In this latest version, type each player's name in the manager sheet and use the drop down menu to assign how many squares to give each player. Then click the Assign Names Randomly button and it will automatically populate the grid with all of the names.

Super Bowl Squares Rules & How to Play


Listed below are the basic instructions on how to play Super Bowl Squares (which are also included within the spreadsheet for the three different versions, 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.

Added again this year is the option whether to keep the same random numbers chosen for all four quarters or to have four different sets of random numbers for each quarter. Using the rotating quarters method, someone could theoretically still get the number 3 for all four quarters, though that’s not very likely, or four different players could each get a 3 for different quarters.


squares excel spreadsheet


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



Super Bowl Squares Template 2019 Download


The beauty of this football bowl manager is you will not have to change or modify any formulas yourself (unless you want to of course). Instructions are included within the Excel file and shows you exactly how to add more players (either manually or by using the button that is linked to a macro). If you enjoy using this sheet football pool manager I would really appreciate it if you would share it with friends, family, and coworkers. 

To be notified when any updates are made to the file or to get a notification when next year's template is available, download using this link(requires an email address):

Download the Super Bowl Squares Template 2019.xlsm file here

Please let me know in the comments or by email which version you'll use to play - printable, pick your squares, or randomly assign names. Or if you have any requests for modifications shout me out!

Monday, December 3, 2018

2018 College Football Bowl Prediction Pool Manager with CFB Playoff Bracket

The college football conference championships were played this past weekend which means the 2018 NCAA college football bowl season is here again! Therefore, it’s time to make your picks and predictions about who you think will win each bowl game. One of the best times of the holiday season (other than giving and receiving gifts) is gathering around the TV and rooting for your Alma mater or hometown football team. This year has the added bonus of not just single bowl games but the fifth year of a four team playoff to determine the national champion.


Features for this year's bowl prediction pool over the previous college football bowl pool manager spreadsheets include the following:
  • Easy method to make each bowl game worth a different point value, so the national championship game and semi-finals can be worth more points, or however you want to customize it.
  • Updated leaderboard tab with new stats
  • Separate entry sheet to pass out to participants or co-workers that can be imported by a macro
  • Complete NCAA college football bowl schedule with game times and TV stations
  • New stat sheet to track each conference's record during bowl season (Go Big Ten! Go MAC!)
  • The bowl prediction sheets include the football helmet designs for every team (taken from the 2017 college football helmet schedule spreadsheet), their win-loss record, and the logo for all bowl games. I added the helmets so those players who aren't big college football fans can pick a winner based on their favorite helmet design!
  • If you click on one of the helmets it should take you to that team's ESPN page so you can learn more about them and see which teams they won or lost to.
  • Download the CFP Pool Manager and Single Entry Form here.

There are now three ways to add participant’s data:
  • Manual entry using the drop down lists
  • Copy and paste from the selection sheet to the bowl manager
  • Use the import macro to automatically import a player’s data into the pool manager by way of a macro
Below is last year's video of how to use the bowl pool manager spreadsheet.


The beauty of this football bowl manager is you will not have to change or modify any formulas yourself (unless you want to of course). Instructions are included within the Excel file and shows you exactly how to add more players (either manually or by using the button that is linked to a macro). If you enjoy using this sheet football pool manager I would really appreciate it if you would share it with friends, family, and coworkers.

I am working a version where you can add confidence picks by ranking the bowls in order of how confidence you are with your picks. I did one for 2017 but have not updated 2018's for this yet.

Here are some of my Bowl Game Observations that may interest only me...
  • Unlike two years ago when there were four teams with losing records, there are none this year, though there are a ton of 6-6 teams (yawn).
  • Last year, there were no undefeated teams in the four playoff spots (UCF was undefeated). This year there are three undefeated teams (and undefeated UCF got left out again).
  • Biggest gap of match-up between number of wins is 10-2 Cincinnati vs 6-6 Virginia Tech.
  • Can you name all 8 teams who have won 11 or more games this year without looking? Four are obviously in the playoff, but what about the other four? One is surprising.

Please let me know if you have any questions, comments, find any bugs, or have any suggestions for improvement. I love that people are using this Bowl Prediction Game to help raise money for charity, that's so awesome to hear! What team are you rooting for?