Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A bit of a complicated Array that I can't figure out! HELP! | Excel Discussion (Misc queries) | |||
HELP with complicated macro | Excel Discussion (Misc queries) | |||
How do I do this complicated macro??? | Excel Worksheet Functions | |||
Help with a complicated array formula | Excel Worksheet Functions | |||
complicated array | Excel Worksheet Functions |