Archive for February, 2009

Excel 2007 Security Issue

Posted on February 26th, 2009 by Joseph Harris

Microsoft has issued a security advisory regarding possible vulnerabilities in Microsoft Excel 2007. According to Microsoft, a vulnerability in Excel 2007 could allow remote code execution which could in turn allow a remote user to gain the user rights of the system owner. The attack appears to primarily be happening when an attacker host a website [...]

Referencing Data in Another Excel Workbook

Posted on February 26th, 2009 by Joseph Harris

Q: Joe, I have data in another workbook that I want to pull into my current workbook. How do I do this? Unsigned A: The easiest way to accomplish this is to open both workbooks. In the workbook you want to pull the data to, enter =, then go to the workbook you are pulling [...]

Up and Down Trend Arrows for Scorecards in Excel

Posted on February 23rd, 2009 by Joseph Harris

For quick and easy up and down trend arrows in your dashboards and scorecards there is a simple solution…Marlett. If you do not know about this trick, you are probably unfamiliar with the Marlett font in Excel. Marlett was created by Microsoft for Windows and is used as the user interface font in many Microsoft applications. [...]

Using Right() and Left() Functions to Remove Text from a String – Excel

Posted on February 19th, 2009 by Joseph Harris

Today I came across a problem I see at least once or twice a week. I wanted to perform some analysis using an employee number. The output from one of my systems included the employee number with GLK_RDT at the beginning of the number. Since I needed the numbers alone, I needed to remove the annoying 7 [...]

Sum by the Color of a Cell – Excel

Posted on February 18th, 2009 by Joseph Harris

Surfing the web today, I came across a great user defined function (UDF) called sumcolor(). This function sums a range of cells based on the color of the cells. My head began to spin with the possibilities! Below is the VBA for the function. If you are unfamiliar with how to install a UDF, here is [...]

Removing #N/A error in Excel using ISNA()

Posted on February 17th, 2009 by Joseph Harris

The #N/A error happens most often when a lookup function cannot find a result. You can manually remove the all of your #N/As or you can stop them in your original formula. A simple and effective way of trapping the #N/A errors and replacing them with whatever you would like is to use the ISNA() [...]