Need Help with Complicated Array Macro
 

I am trying to get excell to look at a sheet of information
Column 1 Column 2 Column 3
Date Activity ID Activity Description
8/25/08 CP1Win20 Begin Winter Carpenter Shop Work
9/3/08 C1801099 Open Building 201
9/4/08 C1802100 Open Helo Hanger

I want to look at this list, and based on a date (9/4/08), I want to be able
to put all of the "activity" descriptions for activities with a date earlier
or equal to the given date (9/4/08) in different rows on the next sheet. I
know that I need an array function, and I have tried to use a couple of
different things, but nothing seems to be working.

Can anyone help me?

Need Help with Complicated Array Macro
 

Try this:

Data on sheet1 in the range A2:C4.

On sheet2 A1 = date of interest = 9/4/2008

Enter this array formula** in sheet2 A2:

=IF(ROWS(A$2:A2)<=COUNTIF(Sheet1!A$2:A$4,"<="&A$1) ,INDEX(Sheet1!C$2:C$4,SMALL(IF(Sheet1!A$2:A$4<=A$1 ,ROW(Sheet1!A$2:A$4)),ROWS(A$2:A2))-ROW(Sheet1!A$2)+1),"")

Copy down until you get blanks.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Jaime" wrote in message
...
I am trying to get excell to look at a sheet of information
Column 1 Column 2 Column 3
Date Activity ID Activity Description
8/25/08 CP1Win20 Begin Winter Carpenter Shop Work
9/3/08 C1801099 Open Building 201
9/4/08 C1802100 Open Helo Hanger

I want to look at this list, and based on a date (9/4/08), I want to be
able
to put all of the "activity" descriptions for activities with a date
earlier
or equal to the given date (9/4/08) in different rows on the next sheet. I
know that I need an array function, and I have tried to use a couple of
different things, but nothing seems to be working.

Can anyone help me?