Copy cell data based on date ranges

[JTopaz]

I'm having a bit of trouble solving this problem (i'm not even sure on the phase I should be Googling)

I need to somehow create an 'automatic excel function' that when I type in certain date values into certain cells Excel will duplicate that data into different cells further down the sheet (dependent on the dates entered)

If you download the attached file, I hope things will become more self explanatory.

So if we take this as an example:

If you look at Week 1 (date range 8th-15th Oct) you will see that there are 38 different companies in the Advertiser field. Each with their own start/finish date.

What I would like to be able to do is have Excel insert the same company names into Week 2's table (date range 15th-22nd Oct) but only when I set the expiry date in week 1 to a date that falls into week 2's range. Not only should the company name be copied over, I need the company name to be shifted by one category. Eg

Week 1's money/tax will move into Week 2's Travel/Africa
Week 1's Travel/Africa will move into Week 2's Sport/Racing
Week1's Sport/Racing will move into Week 2's Student/Competitions
Week 1's Student/Competitions will move into Week 2's Money/Tax

I hope this all makes sense? I'm interested to hear what you guys have to say.