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)

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))

From Charley Kyd's Excel Newsletter.

When readers ask questions that I think others would find useful, I try to take the time to frame my answer as an article.
Recently a reader asked about the XNPV and XIRR functions, which are new with Excel 2007.
These are really useful functions. Suppose you have a column of cash flows and a column of dates. The dates don't need to be periodic. These functions find the Net Present Value and the Internal Rate of Return for that schedule of cash flows.
My reader wondered if there's a way to do the same thing for Future Values.
It turns out that the SUMPRODUCT function can do exactly what he needed. Better yet, this function allows Excel 2003 users to calculate both the future and present values of the same cash flow schedule.
See Find Future & Present Values From Scheduled Cash Flows In Excel 2003 and Excel 2007.