How to update a excel calendar from other sheets in the same workbook?

Hello,

I'm trying to find a way to update an excel calendar from various sheets within the same document.
A friend sent me this excel in the attached.
It works perfectly for 1 sheet but I couldn't figure out how to duplicate several of these sheets and have it update the same calendar.
In fact, I couldn't quite figure out how this wonderful excel works, I don't have any programming skills

Would any of you kind experts please help me?

Thank you so much!
eelin

Wow! So you are the guru behind this wonderful template!

I'm trying to create a calendar for social media posts. My idea is for this workbook to be separated by 5 sheets - Facebook, Twitter, Instagram, Email, Others.
And in each sheet, it will list the different posts for each channel of communication.
This will help me to see what are the content posted in each channel. And in each sheet, I'll be able to record other information that will help me in tracking the effectiveness of the post.

See attached.

There isn't anything like this out there and any app that offer such a service are expensive. I work for a non-profit organisation (www.ourbetterworld.org) and I'm searching for ways to manage our social media calendar efficiently.

cheers
eelin

I still think it would be better to have one sheet for the posts - you can include another column on that sheet to indicate whether it is Facebook, Twitter etc., and you can see all Facebook posts, for example, just by applying autofilter to that column. You can also have another pull-down on the Calendar sheet to select just FB, TW posts, or All in the calendar view.

How many posts are you likely to submit each day - the calendar displays up to 7 activities per day, but I've done others that allow 10 or 12 activities for each day, and even one which allows 100 activities but in ranges of 10, using another drop-down on the calendar sheet.

The calendar display was set up specifically for that first thread, as that is the format that Nancy used, but I think you would want 3 columns per day - Channel, Story and Details.

Hope this helps.

Pete

Hi Pete,

Thank you so much for replying so promptly!
Thanks for your suggestion too, I considered using a single sheet but it will be quite clunky as I'll need to add more details to the columns after and it would be rather difficult to do planning and analysis based on 1 sheet.

If you are willing, I would like to ask you to help on this request with specific requirements and return your effort with some nominal points. Would that be ok with you? Should I put this under commercial services?

cheers
eelin

I don't bother with Commercial Services, but I'll be willing to continue helping you.

I'll need you to answer the questions from above (how many posts etc.) and for you to specify how you want the calendar to look.

I have submitted many calendar files to threads on this Forum, all slightly different, so it will take me some time to review them and choose the most appropriate one as a starting point. Also, I have visitors this weekend, so it might be into next week before I can get back to you.

Hope this helps.

Pete

thank you so much Pete!
I will get back to you asap on the answers and including a draft of how i hope the calendar can look like.

No worries about getting back to me next week, I'm just glad you're helping me!
Will get back to you soon!

cheers
eelin

Hi Pete,

I've estimated that for each channel, on any given day, we could have zero to 5 posts.
In the attached, I've tried to do up how the calendar would look.

As social media and other communication channels are very fluid, I thought having a few rows that can allow manual input would allow forany factors that I may not have thought of now.

I've also included the sheets for each channel. Using Facebook sheet as reference, I've highlighted in red, the columns that would correspond to the cells in the calendar. The difference is Start date and End date instead of just 1 date. The reason is because the same post could be used for several days. Not sure if it is possible for the formulas to capture this. If it is not possible, i'll just enter the same post multiple times.


This document will be updated by different people in the team, so i'll be uploading it to google sheets.
You must know this by now, but in case you don't,your original file works in google sheets!

Please let me know if you have enough information.
Thank you once again! Have a great weekend ahead!

cheers
Eelin

Hello again,

my visitors have left now.

I got email notifications on Friday that you tried twice to send me a PM, but my inbox was full. I've cleared out some entries, so you can re-send the PM if you want to.

My calendars are usually aimed at being printed out on a single sheet of paper if required, but as you will have quite a number of columns as well as several rows then I shall ignore this constraint. I shall give you up to 25 entries per day plus space for 5 more manual entries - the only drawback with this is that if you select a different month then those manual entries will not be relevant, so you will have to manually delete them (and you will lose the information, unless you copy it elsewhere first).

I think I shall bring all the relevant data into one composite sheet - this will be automatic, so you won't have to do anything to that sheet. I can use conditional formatting to give you the colours that you show in your example file. I don't use Google sheets - do you know if the IFERROR function will work?

I did a version of the calendar some time ago which would allow the User to enter a range of dates for an activity and this would be expanded into individual days automatically - I just need to find the file to remind me how I did it !!

I'll enjoy the rest of the weekend, and get back to you probably tomorrow.

Pete

Hi Ron,

Thanks for that - I've posted so many that it can take some time to locate the one I'm thinking of. The one you found has some of the features that I'm thinking of including (i.e. the ability to select All or individual items from another drop-down), but the one I was looking for can be found in this thread:

http://www.excelforum.com/excel-gene...te-ranges.html

where the columns in blue on the Project_tracker sheet automatically expand the date ranges that the User had put in so that there is one entry per day, and the calendar sheet can just show these in consecutive days. In the file that you had posted (probably an earlier one), I had manually expanded the entries as it was fairly easy to do that.

I need to gather a few calendar files together and incorporate features from a number of them for this thread, so your post has helped in that search.

Pete

Hi Ron,

this is the thread that I was really looking for:

http://www.excelforum.com/excel-new-...o-a-sheet.html

Post #2 points to a number of other threads that have variations of the calendar (which I originally put together in about 2008), and the first of these points to the thread in which I posted the calendar that you found, but ef524a in Post #10 of that thread is a more complete version of it. In the thread above I show again how a date range can be expanded (in the file ef615).

Thanks again for your interest - I'll spend more time on this tomorrow (if it is raining).

Pete

Hi Pete,

No worries about the PM, it's the same message as my reply to you about commercial services.
I think IFERROR function works on google sheets.
Looking forward to your reply!

cheers
eelin

Hi Eelin,

Can you tell me how many days (maximum) you are likely to cover in your start and end dates? I realise that I will need to take account of the days spanning two different months if you have a start date of 31/07/2015 and a finish date of 3/08/2015 (i.e. 4 days in total), but is it ever likely to be 30+ days so that it may span 3 months, e.g. 28/02/2015 to 1/04/2015?

Pete

Hi Pete,

It won't be that long. 30days would be the maximum.

thanks again!
cheers
eelin

Hello again,

I know there are many hours time difference between the UK and Singapore, so I thought I'd give you a quick update on progress before I go to bed.

All the functional parts of the file are working, but I have to use a lot of formulae with INDIRECT in them in order to bring the data from the five channel files, and as this is a volatile function it makes the file a bit sluggish. I shall spend some time trying to improve the performance.

I also need to spend a bit of time to ensure that the calendar displays correctly. There is a lot of conditional formatting in the top and bottom rows to hide unwanted day blocks, so it's a bit of a pain trying to get these all working correctly - I may not include the colour coding for the different channels.

I'll be out for most of tomorrow, so I just thought I'd bring you up to date.

Pete

Thank you for the update and your effort Pete. Re the day blocks, i'm not sure I understand what you mean.
But if you mean saturdays and sundays, don't worry about it. we have posts during weekends too, so they can look the same as weekdays.
If i got it wrong, then don't worry about it, you are the expert!

cheers
eelin

The calendar has a fixed layout of days, starting with a Sunday. However, the 1st day of the month selected will not necessarily start on a Sunday, so it looks much better if those days that belong in the previous month are completely blank (i.e. no borders or text/headings).

A 30-day month which starts on a Saturday or a 31-day month starting on a Friday or Saturday will spill over to occupy 6 weeks on the calendar, so it needs to be big enough to cope with this. However, not all months will need all 6 weeks, and indeed, if the 1st February of a non-leap year falls on a Sunday it will only occupy 4 weeks, so again those unused days at the end of the calendar need to appear blank.

All of this is achieved using conditional formatting - the relevant headings and borders only show if the date cell is not blank, so as each day block now has 3 fields of data, I have to set it up individually which is a bit of a pain.

I remember doing one calendar where I took those "orphan" days from 6-week months (there is a maximum of 2) and put them on the first row of dates - you sometimes see this on commercially printed calendars, but there is no great advantage in doing this for your calendar.

Anyway, I'll be going out soon, so there won't be much more progress today.

Pete

Eelin,

If you look at the attachment in my latest post on this thread:

http://www.excelforum.com/excel-prog...ml#post4154255

you can see the effect that I was describing above for the orphan days. This is another calendar that I've been working on over the last couple of days - it's a bit easier than yours, so I finished it first.

Hope this helps.

Pete

Hi Eelin,

this has been a labour of love, but I think I've finally included all the things you asked for.

In each of the five channel sheets I've inserted a new column A to act as a helper column (shown in blue), with a formula in A3 which I've copied down to row 175. As you add more data to each sheet, you will need to copy this formula further down. The formula identifies those records which need to be copied to the Composite_Events sheet, by looking at the date and the channel type selected in the Calendar sheet. Apart from ensuring that you have enough formulae to cover the data in the sheet, you can do what you like with the rest of these sheets to help you track individual postings. You might like to hide column A of these sheets so that your co-workers don't accidentally over-write those formulae.

As mentioned, the relevant records (and only those) are brought automatically into the Composite_Events sheet. There are a series of formulae (in red) in columns F to N which bring those records across. I've copied those formulae down to row 50, though you might need to copy them further, depending on how much data you might have (further comments in the sheet). There is also another set of formulae (in blue) in columns P to U which I've copied down to row 250. These expand the records where necessary so that there is one entry for each day, enabling you to specify a date range in the channel sheets - if the end date is missing the formulae will assume the posting is for one day only. All of this is done automatically, so you shouldn't need to do much on this sheet other than ensure that the formulae are copied far enough to suit the amount of data that you might have. You could protect the sheet or even hide it, so that you don't have any accidental changes being made.

On the Calendar sheet you can select the month and year of interest as before using the drop-downs in cells Y2 and Y3, and I've also added another drop-down in AA2 so that you can select to view an individual channel or all of them. I've moved the MATCH part of the previous formulae across to the right (columns AE to AK) to try to improve performance. For any one day there are up to 25 postings that can be shown, and I've also included 5 blanks at the bottom of each day where you can add things manually (cells coloured yellow). I've used the colour coding from your earlier example to indicate the channel for each posting. I've saved the file with August 2015 as the setting, so you can see how your original test data is displayed (I have changed some of the dates).

As this is a critical sheet with many formulae and extensive conditional formatting, I have protected the sheet using the password Pete_UK. You will find that you can only select cells that are coloured yellow (including the drop-downs) while the sheet is protected, and you will need to unprotect it if you want to adjust column widths, for example, but I suggest that you leave it protected to prevent accidents.

I used a lot of temporary test data while I was building this, and all the functions seem to work as planned. I've removed that test data, so you can add your own and test it thoroughly yourself. I noticed that it seemed a bit sluggish when there was a lot of data, taking several seconds to change the display, but you will have to judge the performance yourself. I have not been able to test it out in Google sheets in a shared environment, and if you do have problems with that then I won't be able to help you.

Let me know how you get on with it.

Hope this helps.

Pete

WOW, this is really impressive Pete!
I'm going to test this thoroughly both on excel and in google sheet environment and get back to you.
I'll also be showing this to the team during our team meeting on Tuesday (it's holidays here in Singapore) and I'd be able to share their response with you too!
OMG, this is exciting, thank you so much again!

cheers
eelin

Hi eelin,

I was just wondering how your testing of the calendar has gone this week?

Pete

Hey Pete,

I introduced it to the team and they love the concept of it!
I've uploaded the calendar to Google sheets and all the formula works! So I'm still waiting for 2 other colleagues to try it out and give me their feedback.
Update you next week!

cheers
eelin

Hi Pete,

Thought I'll give you an update. The team has started using the document and i've gotten feedback that it is very useful while not adding additional work to the existing work process! A very efficient addition to the workflow!

We also encountered an error, but that was due to the sheet being on google instead of excel. When I uploaded the document to google sheet, it seems all the protection on the workbook is lost and the users could insert rows at will. So the formula caught that and the calendar became empty.
I found out the error after much effort and have protected all the sheets/columns to ensure that no one can insert the rows.

So, thank you so much again!

cheers
eelin

Hi Eelin,

thanks for feeding back on the progress of introducing the new file. There are bound to be some teething troubles to begin with, but hopefully you have spotted them early enough for it not to be a major problem.

Over the weekend I thought of a way of avoiding having to use the INDIRECT function to get data from the different sheets. although I've not tried it out yet. As INDIRECT is a volatile function, it will recalculate whenever there is a change in the workbook, so this can slow things down quite considerably if you have a lot of such functions. Let me know if it starts to act sluggish, and I may try out the alternative approaches.

Pete