If the excel sheet properties and columns are not changing and you have and
know how to use access I believe it would be easier to just link that sheet
to a access database and have a couple of queries to pull thte data that you
are looking for.

"Keith" wrote:

In Sheet1, I have records in each row. The following information is part of
each record
Column L (L7:L1000) = start date for each record
Column M (M7:M1000) = initially planned end date for each record
Column N (N7:N1000) = revised end date for each record
[these ranges may contain blank or non-numeric values like "tbd"]

Now in Sheet2, I'm trying to build a list of values to sum up how many
projects were actually active during each week- so for a target date "X",
how many records had a start date before X and the end date or revised end
date after X.
I'm pulling this data from someone else's workbook, so I don't have much
control over how clean the source data is, and I'm trying to build a formula
to get the data we need and I'm close, but don't quite have it. Any help
would be appreciated.

I've started by placing in Sheet2,Column B the comparison dates (1/106,
1/8/06, 1/15/06, etc.)

I'm trying to use an Array formula in Sheet2 (column C) that references the
date in column B to get all records that were active during that span.

The following is intended to determine:
(1) is there a start date (startdate value 0) to make sure it isn't a blank
row
(2) is the start date prior to the date in this same row in Sheet2 column B
(the week I'm trying to pull data for)
(3) is the end date /or/ revised end date after the date in this same row in
Sheet2 column B (showing that the project ended or will end after the week
I'm pulling data for)

{=SUM(IF((Sheet1!L$7:L$10000<=Sheet2!B3)*(Sheet1!L $7:L$100000)*OR(Sheet1!M$7:M$10000Sheet2!B3,Shee t1!N$7:N$10000Sheet2!B3),1,0))}
translated to english: if the startdate <comparison date, and startdate 0,
and either the enddate or revised enddate is greater than the comparison
date, count the record.

There is something wrong with the latter half of this formula, because I get
a running total by week that increments as records are started, but the
running list of values doesn't decrement as records pass their end date.

I also tried a variation of the above, with an enddate checked using
(MAX(Sheet1!M$7:M$10000,Sheet1!N$7:N$10000)Sheet2 !B3) but that didn't seem
to work either.

Any suggestions would be greatly appreciated!
Thanks,
Keith