PLEASE HELP! Finding the right formulas to create the perfect TV Guide!

Hi

WeeklySchedulex, EpisodeGuidex contain a button each

1. Insert in TV Season Guide the shows details
2. Run Episodeguidex Button to generate the Episodes
3. Enter in WeeklyShedulex b4 the any date of the week you want to schedule
4. Run WeeklySchedulex button to populate the Schedule

Cheers,

Wow. Thats great, thank you soo much! I wish I knew how you did all of that! I can conceptualise and format (do that for work), but I cant build!

Would you mind please if I asked you a quick question?

If I was to manually change a date in the Episode Guide sheet and then added a new series (therefore requiring me to press the fancy Generate Episodes button, would those changes be overridden because I refreshed the list?
(e.g. if i update the last 4 episode air dates because there was a change to the schedule in the episode guide list and then added a new series, so pressed the generate button, would the override my changes?)
Would there be a way of stopping that from happening? so only refreshing NEW additions to the list?

Just a question, I am grateful and happy to live with things how they are because its sooo handy, thank you again!

It's like 2.15am here and I should be sleeping (or watching The League) but instead I am so engaged and excited by this!

I think if there was a manual flag that said, do no re-generate dates for this 'series', a flag that you could turn on and off (e.g. if there was a change to the schedule you would update the date, then untick the flag so it would re-generate that would be like a little intelectual software program or something!!!!

Seriously, i looked everywhere for something like this and i hope people are reading this thread and seeing your work (and my design and using this!!)

What do you think?

Well for that you'll need to flag the episode itself and flagged manually.

I include the version that generates the show airing episodes only once (and flags the show details in TV Season Guide). You can then manually change the airing times for specific episodes and add more shows to generate without changing those new airing times. I tried that by adding the Honeymooners and worked.

If you want to reschedule the show completely, just clear the show cell in column F.

Disregard that last sentence, It can be done but needs more coding

You dont know how in love with this spreadsheet I am... I am so grateful!!

the only thing is, the spreadsheet used to refresh the schedule based on the current date (todays date), its not doing that anymore.. i have to manually type in the date..

I have attached my edited version.. have I dont something to it... ooopppss i am so sorryCopy of 2013 TV GUIDE-1.xlsm

Hi Rebecca,

Happy New Year.

I've set up your requirements in the attached file. It's all done by formulae, so it will automatically adjust if you make changes. You may need to copy some of the formulae further down the sheets as you add more data to the file.

Starting with the Season Guide, I've added this formula to H4:

Formula:

Please Login or Register  to view this content.

and then copied down to row 25 (the hyphens help to show this). You may need to copy this down further if you add more data, but it doesn't matter how far you copy it to.

I've also added a new column E to this sheet to enable you to identify series which may be showing on a daily basis, eg. you may have some mini-series which is shown on consecutive nights one week, so you can use "D" in this column to indicate this - you don't need to put "W" for weekly showings as this is assumed to be the default. You can see that I've put some test data in this sheet as none of your examples gave rise to showings this week.

In the Episode Guide sheet I've made use of two helper columns in F and G, and there are also formulae in columns A to D. I've copied these down to row 100, but, as before, you can copy these further if you need to, but there would be no problem if you copied them down to say row 5000 for future expansion.

I've added a few extra facilities to the Weekly Schedule sheet. All the dates can be seen on row 4, with today's date being highlighted. I've also allowed you to enter a date into cell I4 (the yellow cell), so that you could examine next week's schedule if you wish (or any future schedule - just enter a Monday date). If you leave I4 blank then you will see this week's events. All the scheduled showings for the chosen week will appear automatically.

Hope this helps - happy viewing.

Pete

Richard, post #10 is right

I left the date to be inputted manually but just type in Weekly... b2 =today() and it will render the current date

Hi Pete,

Thats a really neat, clean solution. Thank you.

I was thinking of distributing this spreadsheet and this version wouldnt require large instructions.

My only question is, you will have noticed that RCM came up with a way of not regenerating the data so that, if you wanted to manually change a date, e.g. there is a public holiday and for some reason the schedule is out by two weeks (happens yearly with GOT) you can manually go in and edit the dates of the episode list. I didnt think that would occur often, but alas, it does. Your solution is a little different to that RCM.

Would there be a way to achieve this manual change with your fix?

Originally Posted by rcm

I left the date to be inputted manually but just type in Weekly... b2 =today() and it will render the current date

Ahhhh... that makes sense! I get it now. I dont know why i didnt remember that formula.

Did you see the work that Pete did? Thats quite streamlined and hidden (you can hide those columns that the user doesnt need to see).

I am so exceptionally grateful for everyones help (especially your help)! 3.25am and i am still wide awake with excitement and madly entering data into a blank spreadsheet ready to be copied

Originally Posted by rebeccapink

... so that, if you wanted to manually change a date, e.g. there is a public holiday and for some reason the schedule is out by two weeks (happens yearly with GOT) you can manually go in and edit the dates of the episode list. I didnt think that would occur often, but alas, it does. Your solution is a little different to that RCM.

Would there be a way to achieve this manual change with your fix?

Suppose the 4th episode of Red Widow scheduled for 31st Jan gets postponed, so that it is shown on 7th Feb instead. All you need to do is to manually amend that date to 7th Feb, and then the other dates will automatically adjust to a week later. It might be that the 12th episode is also re-scheduled, so again this date can be changed.

Hope this helps.

Pete

Thanks to RCM and Pete_UK for their solutions! I love love love them!

I hope that others are able to use these spreadsheets! If you use them, post and let us know if you like them!

While I am very grateful for RCM's solution, I am going with Pete's, its a wonderful, clean fix and compliments my set up well. Thanks guys!! YOU ROCK and I added to all your reputations!!!

Glad to be able to help, Rebecca - thanks for the feedback and for the Rep points.

Time for you to get some sleep, now, I think !!

Pete

Hi one always learns.

Here a BUTTONLESS Version of the sheet Just add the shows, and change the date,

Glad I could help even if you don't use it