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.

Glasgow Coma Scale Chart

The Glasgow Coma Scale (or GCS) aims to give a reliable way of recording the conscious state or a person for assessment. Many of my recent posts have centered on sports (especially football spreadsheets) so today I thought I would go in a completely different direction. One area I have never touched on in this blog is the medical profession and how Excel is used in hospitals and rehabilitation centers. The Glasgow scale is used in the field of therapeutic recreation, among others.

The GCS scale is broken down into three main behaviors or activities: Eye Opening (E), Motor Responses (M), and Verbal Responses (V). Each of these behaviors can be rated based on some predefined criteria. Each criterion is given a point value from one to six. To obtain the final GCS coma score simply add the results from the three previous behaviors (Summed Glasgow Coma Scale Score= E+M+V). The final score should fall between 3 and 15 points where a score of 8 is the midpoint. Any Glasgow Coma score below the midpoint shows serious damage.

I have made a blank template of the GCS scale which you can download and print out for free.

GlasgowComa Scale download link 1

GlasgowComa Scale download link 2

On a related note, you can also read about and download a template for the Ranchos Los Amigos Scale which is also known as the Level of Cognitive Functioning Scale or LCFS. LCFS is a medical scale used to assess individuals after a closed head injury.

For more information on how to pass the NCTRC exam to become a CTRS visit rectherapycenter.com

Is this information helpful to you? Would you like to see more health and human services related Excel templates and forms? Please feel free to comment below or Tweet me and let me know your thoughts. Your feedback will only help make this blog better.

2012 NCAA Football Helmet Schedule

Doug Woodworth is the creator of the 2012 NCAA college football helmet schedule spreadsheet. I recently had the opportunity to ask him about the origins of the helmet schedule and how he creates this wonderful college football spreadsheet every fall season. Thanks to Doug for taking the time to fill us in on the details!

I have been creating football helmet schedule spreadsheets for MGHelmets.com since the 2007 season. I created only the FBS schedule in 2007 and asked MG for permission to share it with my friends. MG happened to like the schedule and asked for my permission to post it on his website. The following year I added the FCS, and the NFL. In subsequent years I have created numerous special requests to include high school, NCAA Div. II, Div. III, and NAIA conferences, plus the annual bowl games schedule.

As I became more familiar with Excel, I tried to add a new feature or detail annually.

In 2007 I locked the schedule with a password because I did not want users to accidentally move or delete the helmets or text, like I had done many times while creating it. I was informed that many users wished to edit or color-code the schedules to keep track of wins-losses and other statistics, which could not be done if the schedules were locked. The overall design was unimpressive as there was very little color and I had chosen Arial font throughout.

In 2008 I unlocked the schedules and added a 'Place in This Document' hyperlink to the helmets, which allowed users to click their way through the different conferences or sheets. Adding the hyperlinks somewhat locked the helmets to the cells as they could only be moved with a right-click. ScreenTips were added to the helmets to display conference affiliation. The header was changed to display MG's official header. I added some color to the dates and conference/division rows, which gave a better look. Non-formal game indicators were changed to a letter code rather than the MM/DD format, which created less clutter at the top of the cells.

In 2009 the helmets were hyperlinked using 'Existing File or Web Page' which automatically opens the file or workbook of the selected helmet. With many FBS vs. FCS games being played regularly, this feature made it easy for users to click back and forth between the two schedules. This feature was especially helpful when I created schedules for every team of the Ohio High School Athletic Association. 722 teams were divided among 70 conferences, which were divided among 6 separate regions or files, yet they were all interconnected. To accomplish this feature, the hyperlink address must be typed to include the file name, followed by #, followed by the sheet name, followed by !, followed by the cell reference.

For example: example.xls#example_sheet!A4

In 2010 MG updated his helmet template, which displayed very sharp in the schedules. I added new or changed color to several details. The font was changed to Calibri. Comments were added to cells to easily hover and display the venue and location of neutral-site games. I realized that by dragging the helmets into the sheet rather than copying and pasting, the helmet backgrounds showed transparent rather than a white box. When users color-coded their printable football schedules, the entire cell would now be colored. I was finally pleased with the overall design, information, and color scheme.

The design has remained the same since 2010. My method for creating the football schedules has also remained the same.

1. drag the helmets from MGhelmets.com into the home cells in column A for each conference
2. select all helmets and resize
3. select all helmets and center within the home cells
4. type the hyperlink address for the first helmet
5. copy the hyperlink address from the first helmet minus its cell reference
6. paste the hyperlink address to the corresponding helmets adding the correct cell reference
7. type the team name into the home cell after horizontal and vertical alignment are set properly

With the helmets and text in place I begin to copy and paste until the schedules are complete, referencing several websites along the way. I then go back to notate the non-formal and neutral-site games. I also edit the ScreenTip of the helmets in the home cells to display the team nicknames. The 'Freeze Panes' feature is utilized so that the header, dates, and conference/division rows remain at the top when scrolling down.

The schedules have become rather popular. In fact, shortly after the BCS National Championship Game and Super Bowl, I receive many emails from fans asking when the schedules will be complete for the next season. The schedules are usually released in early summer.

I would like to thank the many fans who email with their kind comments and suggestions. I would also like thank MG for allowing me to display my work and creating these awesome helmets. Without him, these schedules would not exist.

Thanks again to Doug for the very informative description. As I said earlier, this is probably the best NCAA football spreadsheet out there and is very helpful for my office football pools. About the only thing it is missing is the NCAA football television schedule.Maybe an addition for next year?

To download the NCAA 2012 helmet schedule spreadsheet visit MGHelmets. Then visit our downloads page for more sports Excel templates. 

You can also download the 2012 NFL helmet schedule here. Which teams are you cheering for?

-Nick
Let's Go Buckeyes! 

Tags: 2012 ncaa football schedules, ncaa football 2012, football schedules ncaa, ncaa football scores ncaa football scores, ncaa football 12 spreadsheet

If you found this blog to be useful at all please use the share buttons below to help spread the word...

Printable 2012 NFL Schedule Spreadsheet

I’ve put together a printable 2012 NFL football spreadsheet complete with the full schedule and team helmets. The Excel spreadsheet has two main sheets, one for the AFC team’s schedules and one for the NFC. Then, within each sheet I break each league down by divisions. I’ve also done the extra work on this NFL spreadsheet to indicate which games are featured on Monday night football as well as oddball days like Wednesday and Thursday night games.

This is a great tool for fantasy football players who can add on to this spreadsheet to help keep track of your stats. It can also be used in office football pools. If you want you can copy your favorite team’s schedule onto a new sheet and print it out to hang on the fridge or office cubicle. The 2012 NFL season is just around the corner (the Cleveland Browns first preseason game is August 10th) so download this printable football schedule today.

Please keep in mind these schedules are subject to change at any time – you never know when some team’s stadium roof is going to collapse forcing them to change the day and site of the game. Another side note: a popular search term on Google is for “nfl football football.” Really? It must be a simple mistake. I tried to make the best NFL printable schedule so please let me know in the comments below if you have any ideas for improvement.

2012-2013 NFL Helmet Schedule spreadsheet.xls 

Visit our downloads page for more football and other sport Excel template downloads. 
Download the NCAA 2012 college football helmet schedule here.

Dominate your fantasy football league by using the best draft guide. What are your fantasy strategies this year?

-Nick
Not Starting Michael Vick

If you found my blog helpful at all please use the share tools below to help spread the word...