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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

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,210

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,210

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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,552
Messages
5,838,063
Members
430,527
Latest member
MyFace2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top