+ Reply to Thread
Results 1 to 4 of 4

Need to pull current dates from list w/many dates

  1. #1
    Registered User
    Join Date
    02-17-2006
    Posts
    2

    Need to pull current dates from list w/many dates

    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).

    \1


    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

  2. #2
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    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

  3. #3
    Registered User
    Join Date
    02-17-2006
    Posts
    2
    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.

  4. #4
    Forum Expert
    Join Date
    09-09-2005
    Location
    England
    MS-Off Ver
    2007
    Posts
    1,500
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1