Spy Journal 3.0 Filtering the web for you

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)

David Gainer posted this article on his blog with the corrections for the Excel 2007 Calculation error we wrote about recently.

Two weeks ago, we posted about an issue involving the calculation of numbers around 65,535 and 65,536. As of today, fixes for this issue in Excel 2007 and Excel Services 2007 are available for download from the following locations:
Excel 2007
64-bit Excel Services 2007
32-bit Excel Services 2007

Last week I was struggling with getting an array formula to work properly with the MAX function.
I had a column of business units, a column of dates and a status column.
I wanted to find the most recent date for any given business unit where the status was a particular criteria.
I did try using Bob Phillip's sum product page and Chris Pearson's array formulas page, but it still wasn't working right. Fortunately Bob emailed me with the answer and explained it very well. (I was missing the IF function).
Heres the formula and what Bob said about it:
=MAX(IF((criteria_range1="criteria1")* (criteria_range2="criteria2")* (criteria_range3="criteria3"), date_range))

Microsoft released Office 2003 Service Pack 3 (SP3) recently. According to their blurb
Microsoft® Office 2003 Service Pack 3 (SP3) represents a major evolution in security for Office 2003. It further hardens the Office suite against potential attacks and other security threats. This service pack also includes fixes that have been previously released as separate updates for Office 2003.
You can get specific information about this update in the Microsoft Knowledge Base article (923618): Description of Office 2003 Service Pack 3