Need to pull current dates from list w/many dates

[mcilpuf]

I'm not sure my subject title was clear but basically I've designed a
list where in Column A we have the date a song was introduced, Column
B the title of the song and in Columns C through X the various days,
weeks, months that the song will be rotated through. For example Song
a was introduced on Feb 9th and it will play that week, the second
week, the fourth week and then once per month for three months, then to
a 6 week rotation for 3 months then onwards until we've reached the time
period in the third and fourth years where it gets played only twice a
year. I have set up the calculations for each of these dates and each
of the songs. Here is a small look at the spreadsheet to at least see
what I'm talking about (hopefully).

[image: http://www3.telus.net/public/mcilpuf/RotationSnapshot.jpg]


However, I now need to pull into another sheet the songs that need to
be played this week and next, as well as last weeks songs. I tried
using the Advanced Filter for this but I must be doing something wrong
because it is just not working. I need to pull the Song title(s) from
Column B and the appropriate date(s) from Column C through X that will
give me a new short list of songs for the upcoming and preceeding week.
Does this make sense? Should I be using something other than advanced
filter? Maybe a Macro? Is the problem because there are so many
columns of dates? Or?

Thank you in advance for any help as I've been going backwards and
forwards trying to get it to do this but I'm not having much luck. 10
years ago I could have done this like the back of my hand and now it's
eluding me.

mcilpuf

 

[Dav]

If your songs are only introduced on one day each week you could create
a new column.

=if(match(date you are interested in,c2:x2,0)>0,1,0) should create a
column of 1's and 0's that can be sorted or filtered

if you enter the date in a single cell eg a1

=if(match($a$1,c2:x2,0)>0,1,0)

should suffice

Regards

Dav

 

[mcilpuf]

Hi,

Thanks for that (and it works btw! ) but I really need only the
song names with a range of one week previous and two weeks ahead to
come up on a new sheet. This is because the person who will ultimately
be using this is very new to Excel and I'd prefer they just be able to
open the sheet up and see at a glance what should be playing. I'm
trying to make it as automated as possible.

Maybe on worksheet 2 I can do a match todays date, then one at -7 and
one at +7 but how do I get it to pull the song names to the new sheet?
Hmmm.

I appreciate any and all advice. Thanks again.

 

[mcilpuf]

Hi,

Thanks for that (and it works btw! ) but I really need only the
song names with a range of one week previous and two weeks ahead to
come up on a new sheet. This is because the person who will ultimately
be using this is very new to Excel and I'd prefer they just be able to
open the sheet up and see at a glance what should be playing. I'm
trying to make it as automated as possible.

Maybe on worksheet 2 I can do a match todays date, then one at -7 and
one at +7 but how do I get it to pull the song names to the new sheet?
Hmmm.

I appreciate any and all advice. Thanks again.

 

[Dav]

If once again you have the date you are intersted in A1 and the data in
d5:x5 something like the following will work it can then be copied down
ans sorted as before

=IF(SUMPRODUCT((D5:X5>=$A$1-7)*(D5:X5<=$A$1+7))>0,1,0)

You may need to change the <= and >= to < and > depending on what you
require

Regards

Dav