Spy Journal 3.0 Filtering the web for you

I have had a bunch of pretty cool Excel things to post up – and finally got around to clearing my flagged items and browser windows.

Conditional Formatting

I have written a couple of articles on Conditional formatting in Excel 2007 with lots of readers comments and requests for help. They are the two most read articles on this site.

• Conditional formatting in Excel 2007 - entire row colours
• Excel 2007 Conditional Formatting

I was very interested then to come across this article on Joseph’s site by Amit Velingkar where he shows you how to change the automatic colour ranges that are used in Excel 2007 for conditional formatting. He even includes some VBA code for this.

VLOOKUP

Dick Kusleika has posted some pretty interesting information about trapping #N/A errors using the VLOOKUP formula in Excel 2007 compared to Excel 2003.

In Excel 2003, the best way to capture this error, is to use the ISNA worksheet function, such as in this example:

=IF(ISNA(VLOOKUP("XYZ", MyTable, 2, FALSE)),"",VLOOKUP("XYZ", MyTable, 2, FALSE))

This checks to see if the formula returns the NA error and, if so, returns an empty string (to look like a blank cell). There is a big downside to this: For each cell this is used in, Excel has to calculate the VLOOKUP function twice. If you have a lot of them, it can really slow things down. Unfortunately, I don’t know of a better way.

In Excel 2007, however, a new worksheet function was introduced. IFERROR not only shortens your formulas, but can speed up calculations. In 2007, it looks like this:

=IFERROR(VLOOKUP("XYZ", MyTable, 2, FALSE),"")

He also does a very good comparison of VLOOKUP, SUMPRODUCT and INDEX when trying to lookup on two columns.

3 days to go for this Competition!

MSDN® is sponsoring a coding contest for Office 2007, named OfficePalooza! This sweepstakes will run two weeks beginning April 20, 2009, and features ten fun Visual Basic of Applications™ (VBA) coding challenges in the form of puzzles and games. Each entrant will earn a chance to win one of hundreds of available prizes, determined by a random drawing at the end of the contest.

Hierarchical Lists

Dany Hoter has produced an elegant solution to a very common problem with hierarchical lists.

The problem: Validating data entry based on hierarchical (parent child) data. The example used is regions and countries but it could be countries and cities, product categories and sub-categories, class and student name, etc.

You want to enter a region from a list of regions and in the next cell you want to select a country but only from the countries which belong to that region.

Go to Joseph Chirilov’s site to read the instructions and download the sample file. I tried it – it works very well – and we will be including this solution from now on when needing to do this.