Sunday, February 26, 2017

How to check if sheet name exists with VBA

In Excel, you can't have two sheets with same name in the same workbook. If you create a copy of a worksheet, Excel will automatically rename the sheet, usually adding a (1) onto the name. Try creating a copy of an existing sheet and see for yourself. As you add more sheets you probably want to rename them to keep your spreadsheet organized.

There are times when you are using a VBA macro to automate processes in Excel when you want to rename a sheet. If the new name of the sheet is unique your program will be fine. However, if the macro tries to rename a sheet with a name that already exists as another sheet name, you could have a problem. One way to avoid this to make your macro more robust is to add code to loop through all the sheet names and check to see if your new sheet name already exists, and if it does then add something onto the name to make it unique.


Sub CheckSheetNames()

Dim i As Integer
Dim y As Integer

‘the new sheet name, what I want to rename the active sheet as
Dim myName As String
myName = "Sheet"

y = 0

‘loop through all the sheets in the activeworkbook
For i = 1 To ActiveWorkbook.Worksheets.Count

‘if my new sheet name matches any of the existing sheet names, then increase the y count by 1
If myName = Left(ActiveWorkbook.Worksheets.Item(i).Name, 5) Then

y = y + 1

Else
End If

Next

‘If y is greater than 0 then we know that a sheet already with that name already exists
If y > 0 Then

ActiveWorkbook.ActiveSheet.Name = myName & “(“ & y & “)”

Else
‘if y is 0 then the sheet name does not exist so we are free to rename it
ActiveWorkbook.ActiveSheet.Name = myName
End If
End Sub


Now you know how to automatically rename worksheets and how to loop through sheets using VBA. Adding simple checks like these will make your code better and more robust, so if others are using it that don’t have any programming experience they are more likely to not encounter any errors.

Tuesday, February 21, 2017

How to link a textbox to a cell Excel Macro

If you’re putting together some kind of diagram in Excel, you may want to have the ability to click on a shape or textbox and have it automatically jump to another location, like a cell. I have always believed it is faster to mass produce things then manually delete what you don’t need rather than manually creating textboxes, and this is another example of that. I would run a macro to create 50 linked textboxes and then if I only use 30 I can quickly delete the left over 20.
For example, the macro creates ten Textboxes and then links them to cells A1 through A10 using an automatically created hyperlink. The two most important expressions we will be using are:
To add and position a new Textbox using VBA:
.AddTextbox(Orientation, Left, Top, Width, Height)
To add a hyperlink to a textbox using VBA:
.Add(Anchor, Address, SubAddress, ScreenTip, TextToDisplay)
How to link a shape to a cell Excel Macro

If you’re not familiar with programming VBA macros in Excel, check out my quick start guide here. Listed below is the complete code with my comments to help walk you through it:
Sub CreateTextBoxes()
Dim i, d As Integer
‘d will be used as spacing to separate the textboxes so they are not created on top of one another
d = 10
‘perform the loop 10 times and create 10 textboxes. Could change this to be more or into a user input box
For i = 1 To 10
'create textboxes in a vertical column with equal spacing using d
       ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 150, 20 + d, 35, 25).Select
   
'add formula to textboxes so the textbox displays the text in the cell
    Selection.Formula = "=$A$" & i
   
'create hyperlink from textbox to cell, assumes sheet name is Sheet1
       ActiveSheet.Shapes.Range(Array(i)).Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:="", SubAddress:="Sheet1!A" & i
   
    d = d + 30
  
Next
End Sub

How to link a shape to a cell Excel Macro Video




If you put some values in cells A1 – A10 and then run this macro, it will create 10 textboxes, and when you click on the textbox it will jump to the location of the appropriate cell.  Download this sample file here.

Thursday, February 9, 2017

2017 NASCAR Fantasy League Manager Spreadsheet

2017 is the fourth year in a row that I’ve made the NASCAR Fantasy League spreadsheet available for download. This NASCAR fantasy game is based on Total Driver Points according to NASCAR’S scoring system. In order for your drivers to score they must be running for Monster Cup Points. Drivers may run in more than one NASCAR division but can only score points in one division.


2017 nascar fantasy league manager in excel


How to Play NASCAR Fantasy in Excel


At the beginning of the year (first race is Daytona on February 18th) participants choose ten race car drivers. These will be their 10 drivers for the entire year - no changing after the entry deadline. All players also submit the three drivers that they think will place 1st, 2nd, and 3rd in the NASCAR standings at the end of the year. They don’t need to have these drivers in their picks, just who they think is going to finish on top. This is used as a tie-breaker.

The fantasy league manager manually enters each player’s picks into the spreadsheet at the beginning of the year (or uses the new entry form to copy and paste entries). Players keep the same drivers all year long. After each race, the manager then goes to ESPN.com and enters the amount of points that each driver earned during the race into that driver’s column. The spreadsheet then automatically adds and ranks each player according to their driver’s scores. I used many of the same formulas found in the leaderboard of my NCAA Bowl Prediction Pool sheet. This NASCAR fantasy manager template can currently handle up to one hundred drivers and one hundred participants without needing to modify a single formula!



This year, there are two options to download the 2016 NASCAR Fantasy League manager:


Download Option 1 (no email required)


Simply click the link below which will take you to the box.com, where the file is hosted. Next, click on the “Download” link in the upper right hand corner. That’s it! No email address is required.




Download option 2 (email address required):


File is hosted on Gumroad. Click "I Want this" then submit your email address to download the spreadsheet. Your email address will ONLY be used to alert you if I upload a new version of the file, either an update for this year or you will also be notified when next year's sheet is ready to go.



Why two links? Sometimes users find small bugs or offer suggestions for improvement and so I update the spreadsheet. I recommend downloading through download link 2 so you will be automatically notified when an update is made, whether for this year’s spreadsheet or next. But if you don’t feel comfortable submitting your email address I understand, which is why download option 1 is available.

I’ve received comments from many readers informing me they like to use these types of sport manager templates to raise money for their favorite charities, which is so awesome! In the future. I will improve the spreadsheet by automating the entry process (I know, been saying that for two years now) and come up with a way to update the driver scores easier. This is just the third iteration of the template and I don’t follow NASCAR myself like I do football, so I’m sure it can be improved in some areas. Please let me know if you have any suggestions!

Monday, February 6, 2017

How to Combine text from two or more cells in Excel

I'm trying out a new idea I have for sharing Excel tips: creating a short but very targeted PowerPoint presentation on Slide Share about how to do a specific task in Excel. Please let me know if you like this format or not.

Did you know that you can combine or merge text from two or more cells into one cell in a spreadsheet? There are, of course, multiple ways to accomplish this task. One way to do this is to write a formula in a third column which uses the concatenation operator, the ampersand (&).

The CONCATENATE function can also be used to join different pieces of text together. However, in Excel 2016 CONCAT replaces the CONCATENATE function. The CONCAT function combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments. Also new in Excel 2016 is the TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined.




Please let me know in the comments below if you want to see more short presentations like this one! I also posted this as a short video on YouTube and on Facebook, would you prefer this as a video instead?

Monday, January 23, 2017

Super Bowl Squares Template 2017

The match-up for the Super Bowl is set which means it's time to download your Super Bowl Squares Template 2017 edition. Tom Brady and the New England Patriots will play Matt Ryan and the Atlanta Falcons in Super Bowl 51 on Sunday, February 5th at 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) that will never make it to the championship (or winning more than one game), 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.

super bowl squares template 2017

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

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



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



Super Bowl Squares Template 2017 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 2017.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.

Monday, December 5, 2016

2016 College Football Bowl Prediction Pool Manager with CFB Playoff Bracket

The conference championships were played yesterday which means the 2016 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 third year of a four team playoff to determine the national champion.

2016 college football bowl prediction pool


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!)
  • The bowl prediction sheets include the football helmet designs for every team (taken from the 2016 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!

There are now three ways to add participant’s data:

  1. Manual entry using the drop down lists
  2. Copy and paste from the selection sheet to the bowl manager
  3. Use the import macro to automatically import a player’s data into the pool manager by way of a macro 
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.

Tweet: I'm using @NTWProductions pool manager in #Excel to predict #cfplayoff & #bowlgames. Get yours here: http://ctt.ec/8EBeO+


Here are some of my Bowl Game Observations that may interest only me...

This year, four teams have made bowl games with losing records:

  1. Mississippi State (5-7)
  2. North Texas (5-7)
  3. Hawaii (6-7)
  4. Central Michigan (6-6 but should be 5-7 because of the fluke win at Oklahoma State)

This could get ugly: Motel 6 Cactus Bowl you have Baylor (6-6) vs. Boise State (10-2) or Birmingham Bowl South Florida (10-2) vs. South Carolina (6-6)

Strangely Intriguing: Popeyes Bahamas Bowl featuring Eastern Michigan (7-5) vs. Old Dominion (9-3). This is EMU’s first bowl berth since 1987 and Old Dominion, playing just their third year at the FBS level, going to its first-ever bowl.

If the MAC’s Western Michigan beats Wisconsin, it will have more Big Ten wins than four teams had in the conference in 2016.


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?

Thursday, November 24, 2016

2016 Holiday Gift Guide for Excel Users

The 2016 holiday season is officially upon us here in the United States which means it’s time for my annual gift giving guide. I used to panic every year whenever my spouse, parents, and siblings asked me what I wanted for Christmas. I needed to give them an idea otherwise I’d end up with an ugly sweater or some random gadget I would never use. So to help alleviate some of my stress I started compiling my own holiday gift guide. It’s kind of like the big toy catalog you used to get as a kid, only this is for adults. I’ve made a list of items I think would be very useful or exciting for your fellow Excel users, sorted by different categories. Some of these items I already use on a daily basis and others are things that are on my own personal wish list. Enjoy!

MY GO TO EXCEL BOOKS


For just the second time ever, I am giving a discount on my paperback book, 76 Excel Tips to Improve Your Productivity and Efficiency. For 20% off, use discount code: 2F5RYRQC when purchasing directly from CreateSpace here: https://www.createspace.com/4754053. Or you can purchase from Amazon.com here.


Excel: QuickStart Guide - From Beginner to Expert by William Fischer. This Excel book covers everything you need to know about charts, pivot tables, data modeling as well as dashboard design. It is ripe with examples and real-world applications. For those starting in any kind of role in analysis, whether it be as a data scientist, financial analyst, research analyst, or just any type of analyst or manager in general, I would definitely recommend this book.



Excel 2013 Power Programming with VBA by John Walkenbach. This book covers all the methods and tools you need to know in order to program with Excel. Provides tips, tricks, and techniques for expanding Excel's capabilities with VBA that you wont find anywhere else

OTHER BOOKS WORTH READING


The Martian by Andy Weir. If you only read one (fiction) book this year, The Martian has to be the one. I absolutely love this book (and it’s even better than the movie). As soon as I finished it the first time, I immediately re-read it – something I’ve never done before. It’s about an astronaut (with a great sense of humor) who gets left behind on a mission to Mars and has to figure out how to survive. If you’re interested in space exploration, problem solving, engineering, chemistry, botany, or disco + 70s TV shows, I highly recommend you read The Martian. Maybe the best book I’ve read in the past five years.




Catalyst: A Rogue One Story by James Luceno. If you’re a Star Wars fan like I am you’re probably anxiously awaiting Rogue One: A Star Wars Story movie on December 16th. This book will wet your appetite before the new movie comes out. The story takes place during the Clone Wars and the initial formation of the Empire. Not required reading for the movie, but will give you some additional background information on the characters.



What If?: Serious Scientific Answers to Absurd Hypothetical Questions. This book contains hilarious and informative answers to important questions you probably never thought to ask, like: What if everyone on earth aimed a laser pointer at the moon at the same time? What if you could drain all the water from the oceans? What if all the lightning in the world struck the same place? Very fun and interesting read!




Will It Fly? How to Test Your Next Business Idea So You Don’t Waste Your Time and Money by Pat Flynn. I’ve been following Pat’s blog and podcast for a number of years, and you might have seen some of his tips at work on my site. If you’re new to the online business world, this book is fantastic. Lots of practical steps to take to prove whether your idea has validity or not.


Another quick, shameless self-plug. Where are the most terrifying roller coasters found? Who designs them? Which park builds the craziest rides? Find out by reading my book The 50 Most Terrifying Roller Coasters Ever Built! Another reason for including this book on this list is to show you a real life usage of Excel. How's that? Because this is one of the books I wrote using an Excel spreadsheet!




TOOLS FOR THE JOB


Laptop Privacy Screen Protector. Whenever I visit a customer I always take my privacy screen protector for my Dell laptop. It keeps your personal or confidential information safe from prying eyes as you’ll see the information on your display while people on either side only see a darkened screen. If you’re ever on an airplane or in a coffee shop and feel like your neighbor is constantly looking over your shoulder at your screen then you need to get one of these today!



Laser Pointer. This laser pointer always comes in handy when it’s time for a meeting or presentation. Plus, it doubles as a toy to keep your cats entertained.



Keyboard Case for Tablets. How do I get so much done, especially when I’m traveling on the road a lot? I use a combination of a Samsung Galaxy Tablet and my new Keyboard case. They’re small so I can take it almost anywhere and the keyboard allows me to do things like type out this blog post, reply to your email questions, and write Excel macro code.



Excel Quick Reference Sheet - Laminated quick reference showing step-by-step instructions and shortcuts for how to use Microsoft Office Excel 2013.

Microsoft Surface 2: There are newer, more powerful Surface tablets available but they're pretty expensive, running around $1000. The Surface 2 is now pretty affordable at around $250 and is still one of the best tablets for Excel and other Microsoft Office products. In fact, it comes with fully functional versions of Excel, Word, Outlook, Powerpoint, and Onenote for free. As far as I know, no other tablet has anything comparable. 32 and 64GB versions are available.

TECH, GADGETS, AND TOYS


Google Cardboard – Google Cardboard is described as Virtual Reality (VR) on a budget. Gives you a taste of the capabilities of the Oculus Rift at a fraction of the cost. Though be warned it may cause motion sickness if the app you’re looking at doesn’t perfectly track your head movement. It’s fun to assemble and play with. And cheap too.



Dash and Dot – programmable robots. If you’re into programming things like Excel  macros and you want to teach your kids the joys of programming then I’ve found the perfect gift for you. These cute robots are designed to help introduce children to the wonderful world of programming. Oh, and they’re fun for adults too! Can’t wait to use this with my son in a few years. For now he just likes watching me drive it around with my phone.



Solar Powered Christmas Lights. I love putting up Christmas lights and trying to out-do my neighbors, but I have to admit I feel a little guilty about using the additional electricity. Luckily I found these solar powered Christmas lights and they actually work very well! They’re environmentally friendly and they automatically turn on and off each night. They also don’t have to be attached to a power source so I can put them in areas I normally couldn’t string lights.



Fitbit Alta. Let’s face it – us engineers nowadays sit in front of a computer a lot. I didn’t realize how much I wasn’t moving until I started wearing a Fitbit. It now helps me to stay motivated by tracking all-day activity like steps, distance, calories burned and active minutes so I can stay healthy for my family. When I’m sick I can’t answer your emails and help you out, so I use Alta to help stay in shape and on top of my game!




Amazon Prime Membership. If you haven’t joined Amazon Prime yet, why not? I do almost all my shopping online and I get free two-day shipping on nearly everything. You can also borrow books, watch movies, and stream music. Click here to start your 30-day free Amazon Prime trial membership.



YOUR SUGGESTIONS?


Are you putting any of these items on your holiday wish list? If so, let me know which ones in the comments below. Do have anything you’d like to recommend to me?