Friday Fun: Spreadsheet Engineer

Gantt Chart Template Pro Giveaway Contest

Gantt Charts are extremely useful tools for planning and  project management. These bar type charts are helpful when laying out schedules and tasks associated with a given project. A spreadsheet version of a Gantt chart is often a cost effective alternative over more expensive project management software (like Microsoft Project).

In my experience, one of the best Excel Gantt charts I’ve used is the Gantt Chart Template Pro from Vertex42. This easy to use tool will make your project planning more efficient. Helpful instructions and an FAQ section are included within the spreadsheet.

You can test drive the free version of the Gantt Chart but you will need the Pro version to unlock all of the features, such as grouping rows. Fortunately, I have a copy of the Pro version to giveaway for free - a $39.95 value! 

Entry into the giveaway contest is simple:

1. Visit Vertex42’s website.
2. Leave a public comment with your name on this blog post about how the Gantt Chart Template Pro will help you manage your project and what you will do with it.

That’s it! But wait. You want even more chances to win? Increase your chances of winning by doing any or all of the following:

1. Subscribe to our free email newsletter to hear about future contests
2. Share this post via Twitter, Facebook, LinkedIn, etc. Be sure to include in your comment how you shared the post so I can credit you for the additional entries.

The contest period starts today and ends Wednesday, September 12th. I will use Excel to randomly selected a winner. Enter now for your chance to win the free Gantt chart Excel template and improve your project planning.  Thanks and good luck!

-Nick
Efficiently Planning Projects

Friday Fun: Thanks for sending the Spreadsheet

I've decided to start a new, lighthearted series called Friday Fun where I will attempt to post a funny Excel related joke, story, or image. Today I've found an all too true eCard from someecards.com. Enjoy!

Excel Create Folder Macro Updated

Today I’m going to revisit how to create a folder in Excel. There’s been some good discussion on my earlier post about to use an Excelmacro to automatically create folders. My original version only created folders in the same file where the Excel spreadsheet was saved. After some reader questions and collaboration we’ve created a new version which allows you to browse to the directory location where you would like the VBA macro to automatically create all the folders you have listed and selected in the Excel workbook.

A reminder of how the Excel macro creates folders. Make your list of folders in any column in a worksheet (which does NOT have to be saved like in previous versions). Select the range of names you want to create. Run the macro.

To open a folder browser with an Excel macro we need to create a shell application object using this code:  

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

I put together a short video showcasing the end result and how the Excel VBA create folder macro should work. Also, if you’re looking to build your own website watch the video to get a 25% off coupon for Host Gator.

The complete code is listed below. Now you can show your bosses and coworkers how to make a folder with Excel. Please join our newsletter for more Excel tips.

Sub Create_Folders()

‘http://excelspreadsheetshelp.blogspot.com

penAt = "My computer:\"

Set ShellApp = CreateObject("Shell.Application").BrowseForFolder(0, "Please Choose The Folder For This Project", 0, OpenAt)

'Set the folder to that selected. (On error in case cancelled)

On Error Resume Next

BrowseForFolder = ShellApp.Self.Path

'create the folders where-ever the workbook is saved

Dim Rng As Range

Dim maxRows, maxCols, r, c As Integer

Set Rng = Selection

maxRows = Rng.Rows.Count

maxCols = Rng.Columns.Count

For c = 1 To maxCols

r = 1

Do While r <= maxRows

If Len(Dir(ActiveWorkbook.Path & "\" & Rng(r, c), vbDirectory)) = 0 Then

MkDir (BrowseForFolder & "\" & Rng(r, c))

On Error Resume Next

End If

r = r + 1

Loop

Next c

End Sub

 

Create Folders.xlsm Download

Join our newsletter for more helpful macros.

Weighted Olympic Medal Count 2012

In honor of the 2012 Summer Olympic Games currently being held in London, England, I decided to create a Microsoft Excel spreadsheet template for the medal count. There are two primary methods most websites appear to be ranking the 2012 medal count. Sites like Yahoo rank countries by the total number of Olympic medals won. Other sites, like the International Olympic Committee (or IOC) rank countries by their gold medal count. (And others, like this one from Forbes, rank by other factors like per capita or GDP.)

If you rank by gold medals countries like Great Britain and South Korea look really good. On the other hand, Japan has 27 medals, ranking fifth overall, but only TWO of them are gold. I’ve devised my own ranking system to give each Olympic medal a weight where the silver is worth half a gold medal and a bronze is worth only a quarter of the gold. Based on this new scoring system, the Olympic results suddenly become quite interesting.
 

I looked at the Olympic Game results for the top twenty countries medal counts up through today (Monday, August 5th). The top four countries actually remain in the same order but Japan drops from fifth to eighth. South Korea jumps up from 7th to 5th due to 11 gold medals. The biggest increase is Kazakhstan which shoots up from barely making the list at #20, almost all the way into the top ten at #11.The biggest fall is by Canada from 12th to 16th. Oh, and if he were a country he’d rank 14th overall because Michael Phelps' medal count at these Olympics Games is four gold and two silver.

I’ve shared my Excel spreadsheet on Google docs and listed out the Olympic medals by country (as of the morning of August 5th - I will try to keep this updated but no promises!). How would you weight each medal against the others? Comment below and share any of your more interesting Olympic medal counts!

Check out our Downloads page for more sports templates and join our mailing list to be notified about new posts and spreadsheets.

How do you create hatching in Excel?

The pattern fill is a great tool to create hatching in Excel 2010 (hatching is when you add fine lines to graphics to represent shading or other factors). However, for some reason the option of filling a series with a pattern was taken out of the user interface in Excel 2007.  The good news is the Excel pattern fill was not taken out of the 2007 object model so you can use VBA to apply a pattern to a series. Andy Pope has done just that by creating an add-on to return the tools for hatching in Excel 2007. We recently had the opportunity to talk to Andy about creating the Excel fill tool. Thanks to Andy for taking the time to answer a few of our questions.

ESH: How long have you been using Microsoft Excel?
A: I started using Excel 5.0 around 1998.

ESH: Could you please explain why the hatching/pattern fill feature can be found in Excel 2010 but was left out of 2007?
A: That is really a question for the people at Microsoft. For Excel 2007 the functionality was there, for backward compatibility, but they did not include any UI for users to employ it. This was either an over-site or a deliberate move to depreciate the feature. The negative feedback to the removal of this feature made Microsoft reinstate the UI for Office 2010.

Excel 2007

ESH: What is an Excel add-on? Why are they needed?
A: Add-ins are hidden workbooks that contain code to extended the functionality of Excel. Normally the functionality they provide is not specific to any one workbook or data set. As with the Pattern Fill add-in it provides the functionality to fill any shape or chart element.

ESH: Could you describe the process of creating the fill add-on? What were the steps involved?
A: First identify a problem or task that can be made easier and or quicker by using VBA code. Create the code required to perform the task. You need to bear in mind the following,

• References should be to the active workbook
• You need to provide UI elements so the user can interact with your code
• You need to handle errors that your code may encounter as you cannot
• control how the user will attempt to use your add-in

Excel 2010

ESH: Great information! Thanks again to Andy for taking the time to answer our questions and for creating such a useful plugin. To download the pattern fill plugin visit Andy’s page here.