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.

I much prefer working in Excel 2007 to Excel 2003. Despite the issues with backward compatibility, there are a lot of advantages and benefits to using the new version.

Some little things that have been changed are

The previous limit on nested brackets in formulas from 7 has been increased to 64. I used this today

The number of columns and rows has increased. I used this today.

I had to develop a file for a client that involved a complex work roster arrangement to calculate days off in repeating 2, 3 or 4 week cycles for the next 20 years.

Here is the nested formula that got me the logic for a roster.

=IF($X5>=AJ$4,$X$4,IF($Y5>=AJ$4,$Y$4,IF($Z5>=AJ$4,$Z$4,IF($AA5>=AJ$4,$AA$4,IF($AB5>=AJ$4,$AB$4, IF($AC5>=AJ$4,$AC$4,IF($AD5>=AJ$4,$AD$4,IF($AE5>=AJ$4,$AE$4,IF($AF5>=AJ$4,$AF$4,$AG$4)))))))))

I then used one formula to generate over 600,000 cells and create a map that looks like this.