Spy Journal 3.0 Filtering the web for you

Virtual side have done an interview with Angus Logan the Senior Technical Product Manager for Windows Live (I know him!)

He talks about:

• Windows Live Tools for Microsoft Visual Studio
• Windows Live Messenger IM Control
• Windows Live Platform Quick Applications
• Windows Live Spaces Photo API
• Windows Live Photo Gallery Beta
• Silverlight Streaming by Windows Live

Jean Philippe Bagel works for Microsoft. He has written some great tips for Excel 2007.

Of course the right click on the sheet tab navigation buttons will work in older versions of Excel and is a great way of navigating rapidly through large workbooks. Navipane provides a great alternative that is not as limited as Excels built in display of available sheet tabs.

Excel 2007 has some very advanced and easy to use conditional formatting and Jean explains one very well. However please be very careful with using this feature in any Excel 2007 workbooks that need to be viewed or edited on a Excel 2003 or lower machine. The conditional formatting in 2007 inst backward compatible. In fact it is quite different in both how it works and how it displays to previous versions of Excel.

You can get a low-resolution e-book version of his new book, Learn Excel
97-2007 from MrExcel.
This is the complete book. It is in low resolution, so it will appear
fine on your computer monitor. It is not suitable for printing.

Andrew recently posted a piece of VBA code used to create dynamic ranges. Very useful.
Here is my original post on dynamic ranges posted back in 2004 repeated.
If you have used a range name before then you will know that it can be frustrating updating the reference of the range name if you want to add data to the range.
Here are some ways you can dynamically update the range by using the OFFSET function in the range name reference.
Assume for all these examples that column A has a mixture of text and numbers for several cells.
Click Insert - Name - Define on the menu.
In the Names in Workbook Dialog box type a range name (Eg test_range) and then try these different options.
1: Expand Down as Many Rows as There are Numeric Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)
2: Expand Down as Many Rows as There are Numeric and Text Entries.
In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),1)