Assistance in creating a detailed parenting calendar based on a common entry tab

Hello,

I believe I am an excel novice to intermediate user, though I think this time I am above my head.

Already created a 12 month calendar, which updates as I add months. Issue is that I currently manually have to put all events on the calendar, sometimes add rows. With lots of school functions, baseball, holiday's and such, this has caused lots of work.

Ex is not tech savy, so I have to create everything and print the calendar pages to pdf then email them to her.

Attached is kind of what I am looking to get from her, the decree and such, to enter, as well an example of the May tab of my calendar.

Is there a way to take this input on sheet 1, auto fill events on the proper calendar, auto shade based on criteria, and fill in end times?

Thanks in advance

Take a look at the attachment in post#4 of this thread:

http://www.excelforum.com/excel-prog...ndar-help.html

It does something similar to what you want, except for the shading - perhaps you could add this via conditional formatting.

Hope this helps.

Pete

Pete,

Thank you, this has some of what I need. Also looked at a few others you have previously posted. Issue I found is that most are for single day only events. In my case most events range from say 6 pm Friday to 6 pm Sunday, though on that same weekend there may be a 6pm to 9 pm event on Friday. (Note that the start and end time can be two appointments if needed, as long as the details in the Start Location and end comments are included, ie 6 pm Child Choir - Drop off at school Door X, 9 pm Child Choir Pick up - School door y. Hope that makes sense).

Looked at A1D Battle Rhythm.xlsm as well, it was helpful in auto populating, though only had one month and again while it listed start times and comments, none of that affected the actual calendar.

Does that help?

Well, with mine you can have up to 7 events per day. If you want multiple months you can copy the Calendar sheet and re-name it to a particular month, and then just have the drop-down in K3 fixed at that particular month for each sheet - you can also just have the year chosen in one sheet (eg January) and have this propagate onto the other sheets in K4.

If you have events spanning several days, then you need to have one entry per day, so if you had Holiday for 7 days, say, you can put the first date in the Activities sheet along with Holiday, then copy Holiday down to the next 6 cells, but for the subsequent dates for that period you just need to put a simple formula in the second date cell (i.e. =cell_above + 1) then copy this down 5 more cells.

I think it will be easier for you to take an existing template and modify it to do approximately what you want, rather than start from scratch entirely.

Hope this helps.

Pete

Pete,

Good Point.

Looking at the list of events going forward, I can see where I will need 10 events per day. Is there an easy way to copy your formula without having to change each one?

Do you know of a way to do conditional formating via code or another method, where on Tab 2 (month) it will color the cell Red if the event on the List tab for that day says "Choir"?

Thanks
Brian

I did post an amended calendar some months ago which had up to 12 events per day. In order to display that in a meaningful way, I arranged for it to be printed in portrait rather than landscape orientation, and to avoid the "orphan" days that you can get on a sixth week in a 30-day month that begins on a Saturday, or a 31-day month that begins on either Friday or Saturday, I also arranged for those orphan days to be appear in the first week's block. There was also quite extensive conditional formatting to remove the lines around any day-block that didn't exist in that month, to give a more pleasant appearance.

Consequently, you would need to be very careful how you applied any extra CF, so as not to upset the existing CF. I thought you might want colours to distinguish between your activities and those of your ex, judging by what you had put in your example file, but if the colours are to distinguish between different types of activity then you would need to have a CF condition set up for each type and a CF formula along the lines of:

=ISNUMBER(SEARCH("Choir",cell))

and with the Fill colour set as appropriate.

I'll try to dig out the file I referred to above, but it might take some time to find it - I've a feeling it was numbered in the 180s (all my attachments are numbered like ef66_filename).

Hope this helps.

Pete

The file I was thinking of is attached to post#4 of this thread:

http://www.excelforum.com/excel-form...e-to-tab2.html

There is a table at the bottom of the Calendar sheet which can be deleted, as this just summarises the types of activity. Also, you can delete that column from the Actvities sheet (they might be called different things, but it's the same function).

Hope this helps.

Pete

Pete,

Awesome, that helps a lot. I like the calendar on the first on you referenced better than this last one, as if you look at June, it puts the 30th at the top.

Played with the first one, got the conditional formatting working, some formula's added, looking good so far. Down to just a few tweaks then I can send it to the Ex, well printed copies anyway.

Items I am trying to resolve.
Is there a way to copy the formulas from the second reference doc to the first, without it referencing to the second, for lines 8 through 12 on the calendar?

Is there a way to add some basic logic to the cells, say if the term "birthday" is in a cell that it looks to see if it is Saturday or Sunday and if it is it adds the data "9 am to 9 pm" otherwise on finding "birthday" it adds "5 pm to 8 pm"? If that's not possible with your format, that is fine, I can manually do it, just thought I would ask. (I am curious, can you do conditional formatting to Strike through or remove an item on June 1 in line 1 if anything in lines 2 to 12 say "vacation"? Not saying I am wanting to go there, more just curious if that is even possible.)

My expertise is more with some basic VBA (Locking some cells, auto populating a cell based on a user selected drop down), using Vlookup to copy data from a table based on user selection, that type of thing. Calendars and conditional formatting is new to me.

Thank you for all your help.
Brian

So, you want the first calendar but with up to 12 events per day? Leave it with me and I'll set that up for you (I'm going out this afternoon). Or, if you have already set up the conditional formatting it might be better for you to post that so I can add to it.

You could have another column for the description to put in basic things like birthday, and then in the other column (the existing one) you could have a formula to check for Birthday and the date being a weekend and add the appropriate time, and similar for other "events" that you want to do this for.

Not sure about removing items if you have Vacation - what if there was a birthday that week, you would still want to show it.

Hope this helps.

Pete

Pete,

Sure thing. Here is where I left off last night.

Thank you
Brian

Hi Brian,

I've set this up to give you up to 12 events per day, and the conditional formatting should apply as you had set it up.

Of course, this means that the calendar will be longer, so the appearance on the print out might need some adjustment (make the columns wider, or change to portrait etc). I've added a couple of columns (M and N) which might assist in this. Both have formulae and to use them you just unselect zero from the filter pull-downs in M2 and N2 - Column N (Month Filter) will hide any weeks at the bottom of the calendar which have no dates (e.g. the 6th week of most months). Column M is more dramatic - it will hide any rows which have no events in them, so it will squash up the display quite considerably. You don't need to use these, but now you have the option to do so if you wish. You should remember, though, that filters are not dynamic, so if you add some more data you will need to refresh them to ensure you are not missing anything. It would be better to apply them immediately before printing.

I've also set up two more monthly sheets, February and March, and removed the data validation in K3 and K4. K3 now contains the month number (fixed for each month), and K4 contains the year from the January sheet. You can still use the January sheet to select any month or year, but if you want to have a full year's worth of events, then you just need to copy the March sheet several times, and then change the month number in K3 as appropriate.

Obviously, this is a universal calendar, not just limited to 2013, so you can just keep adding events and dates in the Activities sheet for years to come - just change the year in K4 of the January sheet to display a new set of calendars.

Hope this helps.

Pete

Pete,

Awesome, thank you.

I will play with trying to modify the Birthdays with VBA, but otherwise I like this a lot.

Thanks
Brian

Hrm, sitting here thinking about this, if I decide to track Doctors appointments on here, and add a column on the Activities tab to denote "Visitation, Non-Visitation" event, using a Vlookup and adding a column on the calender, think it would be a pain to do this, so I could make a calendar filter to show just visitation vs appointments?

Sorry, I forgot to add the bit about birthdays - have done so in the attached. I've inserted a new column D (Display Activity) and put this formula in D3:

=C3 & IF(ISNUMBER(SEARCH("birthday",C3)),IF(WEEKDAY(B3,2)>5," 9am - 9pm"," 5pm - 8pm"),"")

and copied down. You can see the effect by scrolling down to where you have the birthdays listed, and if you select April or August in the January sheet you can see the changes (you might need to widen the columns slightly). I've applied a pale yellow background to the B and C columns in the Activities sheet to act as a reminder that you should enter data only into those columns. One drawback with this approach is you would need to copy the formula in column D all the way down to cover your data, so if you were to add new items then you would have to ensure that the formula was copied down further (or copy it down much further initially, as in column A).

You could, of course, add other things to that formula by having &IF(...) at the end.

I'm not sure what you mean about the doctor's appointments.

Hope this helps.

Pete

Since I am trying to track everything in one document, some things will be visitation events were the EX will have to be involved, others will be informational for both parties, such as a doctors appt or choir practice as example.

Would like to add a way to track if its a Visitation or non Visitation event, say be adding a field on the activities page if needed, have the calendar show everything, but also be able to filter by one or the other if needed. That make sense?

Looking at what you have done, I have already learned a lot. Thank you.

Brian

Off the top of my head:

Insert a new column D in the activities sheet, use this to record Joint or Single (or should it be Visitation / non-V ?), then you can add to the formula in the Display Activity column to be able to make this distinction (on the calendar display).

You can set up filter columns like the two I already have, using some kind of conditional formula - just ensure that it doesn't block out the date lines when you apply the filter (check out mine - I have different formulae for the date lines).

(Sorry, a bit vague, but I'm not sure exactly what you want, and I'll be going out soon).

Hope this helps.

Pete

Ah, I think I know what you are getting at. I amended the calendar file some years ago for another poster. He wanted to display training courses, but there were a few different divisions or Training Areas which put on the courses so he also wanted to be able to select by a division or by All. The file is attached, and you can see another drop-down in M3 of the Calendar sheet where you can select the division. The activities sheet has a few more columns of formulae and another column where you can enter the division. The formulae on the Calendar sheet are much more complicated in order to reflect what has been chosen in the new drop-down.

Is this the kind of thing you were thinking of? Instead of division (or Training Area) you could have something like Joint, Me, Ex and then be able to select those from the new drop-down (as well as ALL). I think this will give you the flexibility you were hinting at, although it will take some time to incorporate it into what we have so far. Before embarking on that, it might be better to reflect on EXACTLY what you want to get out of it, so we can build everything in at one time.

Hope this helps.

Pete

Pete,

Yes exactly, looking to filter by My Parenting time, her parenting time, Kids School activities, kids medical appointments, holiday time. That type of thing.

Combining the two docs should meet that, though I was already trying to create a nested if statement, based off looking at yours. Granted not done yet, though look at E 2 on this sample. May not be the best way to do it after thinking on it, but its where I was at so far.

Will try to flow chart out my thought in my head, if that would help . :-)

Thanks
Brian

Here is my thought process regarding the tracking, noting that it does not detail the normal visitation we discussed earlier, as to weekend rotation and midweeks (we have that covered I think).

There are two independent variables to be filtered on there - the person involved (Joint, Me, Ex) PLUS activity-type (School, Medical, Holiday etc).

Also, if you use word-wrap to display long entries then this does not automatically adjust. With your latest attachment 3 rows are used to display the example, but then switch to February and the row height is wrong. Maybe better to switch word-wrap off, adjust row height to normal, adjust column widths to 265, adjust screen zoom down to about 80% (depending on your monitor), use filter in N2 to deselect zero, then do print-preview to see what it looks like.

Hope this helps.

Pete

Pete,

I have been sitting here working on making a HUGE line in the formula bar, when it hit me, It might be better to create named lists and change the formula to do three searches or less. Keep the Birthday search, but change the others to be just one. Based on data in D, compare to named lists. If you match List 1 then put Visitation, if you match list 2 then put school function, if you match list 3 then put Extracurricular, etc. (I think there would only be 4 lists truly.)

Doing lists is easy, though how to I tell your search function to compare D to the lists?

You were right on the cell width, adjusted that on my latest. Was more concerned with the Activities tab first, the display / print was the last on my mind.

Thanks
Brian

Pete,

Doing searching found that I can not do multiple validations in the same function with the way I had it. SO, I swapped to using Count, IF and Search. Document attached.

Believe this resolves almost everything. Conditional formatting I will adjust later. One error that I am missing though. When it is not a birthday, weekend or mid week, and noting is in column D, I get the word False in column E. missing as to why though.

Since I have lists defined, even when manually adding those words to my function, can I use that list to filter events from showing on the calendar? If not, then that's fine, I will deal with it, if so, great.

Your help has been awesome, thank you again.

Brian

Pete,

Fixed my false issue, missed a ,"" at the end of my formula.

Getting so close, will work on it more today.

Thanks
Brian

Okay, so I have added lots of stuff to the document, and determined the proper cell size, and did some combining of fields.

Playing with the sorts and functions, I want to say I am 90% happy with this.

Looking to add one last ability on the month tab (and maybe remove one). Sorting by week is perfect. Sorting by Month I think will never be used. The last function I want to implement is adding a Share / Hide tag on the details, which I can sort by on the Month tab. This will allow me to put things for me on here, and Print everything out for me, but filter it to only show the Share and print just the calendar for her.

Is that something doable? or is that beyond basic functions in Excel?

Thanks
Brian

Hi Brian,

it's difficult to imagine exactly what you might have been doing in the last 10 days, but if you are happy with how it works then fine.

If you want to implement a hide/reveal feature on individual items then I think you could do this. In the Calendar sheet you would need to use one cell to indicate if you wanted to show only the Shared activities, or if you wanted to show both the Shared and the Private ones (i.e. a simple toggle). In the Activities sheet you could use a new column (let's say "Hide") to identify for each item whether that is for sharing (S) or if it is private (P), i.e. for your use only. Then the formulae which derive the date_type_sequence could be amended, such that if the Hide column is "S" then work as before, but if it is "P" AND the cell in the Calendar sheet shows "S" then return "" so it will not be picked up on the Calendar - if it is "P" AND the cell on the Calendar indicates "Both", then the formula can work as before. So, you have to supply the data for each item in the Hide column, and then there is a bit of logic to be applied to the formulae in the first 3 columns (or you might have moved these).

Hope this helps, and good luck - will your Ex appreciate all the work you have put in?

Pete

Pete,

Thank you. I will work on that and see what I can do.

The situation with the Ex is very complicated. While I am doing this to help track things, and give her a readable calendar, it will ultimately be provided to a Court ordered person as well, to "monitor".

This is all to help keep things civil and hope that she will be held accountable for missing visits.

Your help has been awesome, thank you again.

Brian

Pete,

Thank you. I will work on that and see what I can do.

The situation with the Ex is very complicated. While I am doing this to help track things, and give her a readable calendar, it will ultimately be provided to a Court ordered person as well, to "monitor".

This is all to help keep things civil and hope that she will be held accountable for missing visits.

Your help has been awesome, thank you again.

Brian

I have the same request about inputting information on one sheet and having a calendar populate. I tried to use the ones that I have seen in this thread but I would have to change the entire layout of the sheet that I am currently using and I don't know how to make it universal and dynamic enough to make it work. I don't need to capture all the information on the calendar just the project number and milestone on the date needed. I also don't know how to add a duration for the day. for instance if there are 5 things that need to get done on one day two of them take an hour a piece and the other ones take 2 hours a piece. you can see an example of that under the Hour sheet by week. I need the same information on a weekly view and a monthly view. Does anyone have any ideas?
Thank you
Bill

Hi Bill,

If you look at the Forum Rules at the top of the screen you will see that it is not allowed for you to post a question in someone else's thread - you should start your own thread, and that will bring it to the attention of more contributors.

Pete

I am very sorry to interrupt.

Pete,

Was looking at the Calendar tab today, when a Thought came to mind. Think it would be possible to use a Nested IF with what you already have, where if the Activities tab has the word "Fred" in it, look at Cell N4 on the Calendar tab if value = "Show" then the if will show everything, if it does not equal show, then that activity is not listed?

Tried a filter option, ran into issues where it would filter the row, not the item.

If its not doable, thats cool, just wanted to ask.

Otherwise, I did make more changes adding a few columns, where coloring was based on drop downs, and combined some fields to show on the calendar. Effectively, I believe this is done with all the features I needed. Right now its more ease of use type items, or additional details. Wanted to thank you again for your assistance.

Thanks
Brian

Hello again, Brian.

That sounds do-able, although I don't know all the ins and outs of what you've done recently, so I can't advise a particular formula. Perhaps you could attach the latest version of what you have been working on?

Pete

Pete,

Here is the latest update, April being the best example I believe.

Thanks
Brian

Pete, I have been using this for years and LOVE it. I did revert to using the 7 event version over the 12 event version.

Since I am adding stuff today for 2018, I ran into a snag that I cannot recall. How do I add more years to the drop down in K3 on the month tab? I know I added 2017 before, but for the life of me cannot not recall how.

Do you recall how to add more years to the drop down, so that calendar keeps updating for future years?

Thank you
Brian

Pete,

Nevermind, I am stupid, forgot it was not a list or name, but rather a data validation drop down. I clearly should have some coffee before working in excel.

Again, love the design you created, thank you again.

Brian

Pete,

Nevermind, I am stupid, forgot it was not a list or name, but rather a data validation drop down. I clearly should have some coffee before working in excel.

Again, love the design you created, thank you again.

Brian

Nice to hear from you again, Brian, and it's good that the calendar file is still getting used nearly 4 years after we worked on it together.

You might like to put a note to yourself in the calendar sheet to remind you what to do next year to extend the year ranges, but glad to hear that you sorted it in the end.

Cheers,

Pete