listing active campaigns

Sethrow

New Member
Joined
Nov 15, 2005
Messages
10
Ok I have a very long list of campaigns that have start and end dates these are stored in sheet2. The important data is column A has name column B has start date and column C has end date.

I want sheet1 to list only the campaigns that are active, ie where the current date is between the start date and the end date.

Anyone got any ideas?

Also I want it to update everytime I open the file, is this possible? If so how?

Many thanks

Seth

P.So There is more to this problem but I think if get this bit then I should be alble to get the rest working! But don't be surprised if there are follow up questions lol
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
First add a helper column next to the last columnn in Sheet 2,

Assuming that your first row contains column titles and the table is current in A1:C1000, then in the first cell of row 2, enter:
Code:
=IF(AND(B2<=TODAY(),C2>=TODAY()),"Ok","")
, copy that down (you can hide this column if desired).

Then in sheet 1, Cell A2, enter:
Code:
=IF(ROW(Sheet2!$D2)-ROW(Sheet2!$D$1)>COUNTIF(Sheet2!$D$1:$D$1000,"Ok"),"",INDEX(Sheet2!A$1:A$1000,SMALL(IF(Sheet2!$D$1:$D$1000="Ok",ROW(Sheet2!$D$1:$D$1000)),ROW(Sheet2!$D2)-ROW(Sheet2!$D$1))))

Remember, to adjust ranges to suit (i.e. replace 1000, with the number of records you have)

Confirm this formula with Ctrl+Shift+Enter, not just Enter and then copy it down as far as your list in Sheet2 is long.

These formulas will self adjust according to current date.

Note: If you want to also pull the corresponding start and end dates, then just copy the big formula over to the next columns. You may have to format those date columns as Date.
 

Sethrow

New Member
Joined
Nov 15, 2005
Messages
10
Aladin Akyurek said:
Sethrow said:
Ok I have a very long list of campaigns that have start and end dates these are stored in sheet2.

...

How long?

About 1000

Guess its not really that long in excell terms but its long for me when looking thru it by hand.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Sethrow said:
Aladin Akyurek said:
Sethrow said:
Ok I have a very long list of campaigns that have start and end dates these are stored in sheet2.

...

How long?

About 1000

Guess its not really that long in excell terms but its long for me when looking thru it by hand.

Sheet2 (source)
ExtractActiveEventList.xls
ABCD
112/4/20050
2EventSdateEdateIdx
3event-13-Jan-0519-Jan-05 
4event-134-Jan-0514-Jan-05 
5event-164-Jan-056-Jan-05 
6event-9872-May-0518-May-05 
7event-9882-May-053-May-05 
8event-9892-May-054-May-05 
9event-10223-May-059-May-05 
10event-10233-May-0525-May-05 
11event-10243-May-0524-May-05 
12event-10254-May-057-May-05 
13event-10264-May-0512-May-05 
Sheet2


A1:

=TODAY()

D1 must house a 0.

D3, copied down:

=IF(A3<>"",IF((B3<=$A$1)*(C3>=$A$1),LOOKUP(9.99999999999999E+307,$D$1:D2)+1,""),"")

Sheet1 (destination)

A1:

=LOOKUP(9.99999999999999E+307,Sheet2!D:D)

A3, copied down:

=IF(ROWS($A$3:A3)<=$A$1,MATCH(ROWS($A$3:A3),Sheet2!$D:$D),"")

B3, copied across to D3 then down:

=IF(N($A3),INDEX(Sheet2!A:A,$A3),"")

It would be nice if someone would provide code which, triggered by a change in A1, would copy the formulas in A3:D3 A1 times downwards.
 

Sethrow

New Member
Joined
Nov 15, 2005
Messages
10
Wow impressive work

Great work NBVC and Aladin Akyurek!! Both your methods appear to work, I dunno which works better yet but thanks to both of you. I'm gonna be working on this properly very soon and will return with any problems I had with either method as that can only help us all lear.

As Aladin Akyurek has said it would be really useful if someone could come up with code that could automatically fill the column up based on the number in A1.

Thanks again and I'll be back soon once I've had more of a play and thoroughly tested them both.

Cheers,

Seth
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Re: Wow impressive work

Sethrow said:
Great work NBVC and Aladin Akyurek!! Both your methods appear to work, I dunno which works better yet but thanks to both of you. I'm gonna be working on this properly very soon and will return with any problems I had with either method as that can only help us all lear.

As Aladin Akyurek has said it would be really useful if someone could come up with code that could automatically fill the column up based on the number in A1.

Thanks again and I'll be back soon once I've had more of a play and thoroughly tested them both.

Cheers,

Seth

You're welcome.

In case anyone wonders the difference is in speed.
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
Why not just use an advanced filter with the appropriate criteria set up 7 copy the results somewhere else:
Book1
ABCDEFG
1CampaignStartEnd
2a1/01/20012/01/2002FALSE
3b1/01/20051/01/2006
4c1/01/20061/01/2007CampaignStartEnd
5d1/01/2006b1/01/20051/01/2006
6e1/01/20055/12/2005e1/01/20055/12/2005
7fg5/12/20055/12/2005
8g5/12/20055/12/2005
9Database
10Criteria
11Results
Sheet1
 

Sethrow

New Member
Joined
Nov 15, 2005
Messages
10
Confirm this formula with Ctrl+Shift+Enter, not just Enter and then copy it down as far as your list in Sheet2 is long.

What exactly does confirming with Ctrl+Shift+Enter do?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,234
Messages
5,571,045
Members
412,359
Latest member
misstoffeepenny
Top