Formatting Data of an Auto Populating Calendar Workbook

I figured out how to add the extra line.

However, I'm still stuck on how to share this.. This will be completely in vain if we can't share it.

Somehow the share function magically started working.. so nvm on that.. Working on the conditional formatting and having the job span the calendar to the End Date.

What I have done in the past for posters who want to use a date range (i.e. From and To dates) instead of a single date is to generate another table made up of single dates, and then use this as the source for the calendar. This is done automatically.

I'm just about to log off for a few hours, but here is one that illustrates what I mean. I'll check back later to see if there are any further comments.

Hope this helps.

Pete

I've copied your data across to my file from Post #7, so you can see it in operation. Up to 10 "events" can be displayed for each day, and you can choose the month and year in the yellow cells in column K.

Conditional formatting is applied to give alternating blue shades (for clarity), but these are not linked to the names (you would need a lot of colours, and therefore CF rules, for that).

Note that "orphan" days at the end of the month are shown on the top weekly block, so you only need up to 5 weeks to display each month. This happens about 2 or 3 times a year.

This is all done by formulae, and you just keep adding data to your table as required. Consequently, I have saved the file as .xlsx - I noticed that your file was .xlsm, but I didn't bother looking at the macros in it.

Hope this helps.

Pete

Okay this is awesome! I just uploaded the most recent file with all the conditional formatting!!
The only thing I'm unsure of with the CF is if it IS NOT any of the crews with particular formats (our in-house crews), I would want it formatted as a separate color. So I need to change that CF formula you gave me to an IF NOT TRUE of "Alonzo", "Chepe", etc.. RETURN whatever other color I choose to set it up as.

To answer your questions, I just started filling in the template I dl'd from this forum. I couldn't figure out how the Calendar was referencing the columns because it didn't matter where I put the column. So I just kept moving it around until we got it to where we wanted it. We decided we only want the PO & Name on the calendar, so we really don't even need it to reference 2 columns, but I don't know how to change it. Maybe if I could, that would erase the need for column N.

And as for Pete's workbook, I'd like to port the Event_Data sheet into my current version of the Metal Production to accomplish the auto-fill of the multi-day jobs into the calendar. I prefer the 6 weeks, so we can see the overlap of the schedule from previous and next months. I also like the drop down for choosing the months.

Okay, I copied the array into my Metal Production Sheet. Now I just have to get the calendar I have set up to reference that instead.. Then get that last CF if it isn't one of the in-house crews.

Originally Posted by JohnTopley

For other than "in-house" crews use a generic name if you are using a ingle colour. the "NOT" option is "no-go"!

Re column N, why change (you have 16K columns to play with): you need this data for the "lookup" against the calendar so the other option is to manually enter the "PO" & "Name" combination.

Ah! Brilliant. That definitely would just make it easy.

Re column N, I just meant I could combine A & B to make the same thing, then reference that instead of making the new column for reference. Only problem is how the calendar is currently set up to populate; it needs to be two columns.
However, we need to change it anyway to populate from the array that breaks down the jobs into multiple single entries for the multi-day jobs.

That's why I've not really chipped into this thread today - you already have most of what you want in my workbook, which can then be more easily modified to give you the CF colours for the crew. You could also have the crew names listed for each line within each weekly block, and then the colours are even more-easily applied. It is also quite easy to have a static 6-week block, with non-month dates greyed out.

I don't understand why you seem to be wedded to the earlier file that you downloaded.

Pete

I am also running into some issues when I try to delete a line from the data (I:U or even the entire line). Specifically when I delete U (the Start Date) it returns the whole Event_Data array as #REF!.

What am I missing?

Originally Posted by Pete_UK

That's why I've not really chipped into this thread today - you already have most of what you want in my workbook, which can then be more easily modified to give you the CF colours for the crew. You could also have the crew names listed for each line within each weekly block, and then the colours are even more-easily applied. It is also quite easy to have a static 6-week block, with non-month dates greyed out.

I don't understand why you seem to be wedded to the earlier file that you downloaded.

Pete

Honestly I just don't know how to edit the format all that well and I put so much time into the other one, I just figured it would've been easier to edit that one.

But in reference to the CF having the crew names listed for each line, I thought about that. However, we run into the issue when they have more than one job in a day.
You say all this is easy, but easy for you is not easy for me. O.O lol

I very much appreciate both of your help thus far.

I have been making some changes to my file from Post #10 in line with what you are asking for. It is not finished yet, but I have to go out soon so I thought you might like to see the current status.

I've set the calendar up so that it displays 6 consecutive weeks, with dates before and after the month selected via the yellow cells in column K. The 1st of the month will always appear in the top week, so there may be a few days before that and at the end of the 6 weeks which relate to different months, and these are greyed-out, as in your other calendar. Up to 10 different "events" will be displayed for each day of the calendar.

I've inserted a new column (H) in the Events sheet to pick up the Crew member in the expanded dates - this will be used for the conditional formatting. At the moment, the CF on the calendar still shows alternating shades of blue, but you will see a Key that I've set up in column K to indicate a colour for each member of the Crew. I shall set this up later, though it will be a bit tedious to set up 10 different rules.

Hope this helps.

Pete

I think this is more-or-less finished now - time for you to give it a thorough testing out.

I've left Chepe C in, but you can always re-use that name for someone else if you like.

There's not much more to say about it. The colour coding is as per the Key in column K. I've set up two named ranges Ref and Crew on the Events sheet, which the CF rules refer to.

Note that the formulae in the left hand side of the Events sheet have been copied down to row 1000, but you may copy them further (and adjust those named ranges) if you need to. Bear in mind that a single record in your data set gets expanded for as many days that there are in the date range, so if you have a date range which covers 30 days, then that will need 30 records in the expanded set. Thus, you can very easily use up the 1000 rows that the coloured formulae have been copied down to.

In operation, you would add data to your data table (columns J to V in the Events sheet) in any order, and then it will appear on the calendar on the correct date(s). You can omit the End date if the event is just for a single day (i.e. it will assume 1 day duration if the end date is blank). You don't need to do anything with the formulae in columns A to H (other than ensuring that they have been copied down far enough to cover your data), and you can hide these columns if you like to avoid them being changed accidentally.

So, over to you now - let me know how you get on.

Hope this helps.

Pete

I hope you haven't made very many changes to the earlier workbook, as I spotted a few things that needed to be changed, so you should use this version instead.

I've corrected the first date calculation, as well as tidied up a few old named ranges. I've also amended the CF rules - earlier, I had hard-coded the names within the rules, but now I have linked them to the names in column K on the calendar. There is no visible change to what it looks like, but now if you had a new Crew member (e.g. "Dave"), you could just put that name within the Key (e.g. to replace Chepe C), and the CF rule would respond to it.

Hope this helps.

Pete

You're the man!

I will check it out today and let you know if I have any questions or run into any issues.

You're really a life saver. I appreciate you more than you know.

So off the bat, I'm still having the #REF! issue with B:H when I try to delete a line in J:V. Specifically, the problem happens when I delete the "Start Date" (column Q).

I was looking at the formulae, and I couldn't figure out why it was having the issue.

Are you deleting the cell contents, or the complete row?

I designed the formulae assuming that the raw data was contiguous, so I didn't expect any data to be deleted.

Pete

I've tried both ways. It was just a duplicate line, so I was trying to clean it up.

Otherwise the data being contiguous shouldn't be an issue as we would just add it at the bottom and use the filters to arrange as needed.

However, I do see mistakes happening and potentially needing to delete data in the future for various reasons.

The system for issuing email notification of replies is not working at the moment, so I have only just seen your reply. One way to avoid having to delete a record is to copy everything below the offending row in your data table (plus one blank row), and then paste the copy over the offending row.

Another thing I thought of to enhance the calendar sheet was to produce a summary table next to where the key is, so that you would have weeks 1 to 6 in consecutive columns, and then for each name you could sum the days within each week using a SUMIFS formula, and then have totals across and down so that you could compare the workload for each member of the Crew.

Hope this helps.

Pete

Okay, that should be a good enough work around for getting rid of rows. Thanks.

I'm having trouble visualizing what you mean. I think you mean a table with the number of jobs each crew would have in a given week?
If that is what you mean, I think it's a cool idea, but not really necessary for our needs.
Each crew is only ever working one job at a time. The number of jobs they have in a week (or even day) just depends on the scope of the job.

So I can't get the VBA code that I found to do what it is intended. (Really I can't get it to do anything..) I will upload the file to see if I'm missing something.

However, I do have a couple concerns, even if I can get it working:
1) When you open the file, it prompts you to enable (or not) Macros. If the user chooses not to enable, will that make this whole thing void?
2) Will this code work once it becomes a sharable file and it's auto-saving? I know we can set it to where the users only have access as read-only.

Whoops. The password is just "test"

I had an idea that if we can't get the permissions how we want them, maybe we could just separate them into two separate workbooks.
Even though that would be a pain.
However, idk how/what to change in the code on the calendar sheet so it would still correctly reference our data spreadsheet.

The easiest way is to arrange the Excel window so it does not cover the whole screen, and then you can just drag the tab for the Events sheet to an area outside the Excel window. This effectively moves the tab into a new file window, so you can use File | Save As to save that new file with a new name. You could also use File | Save As to save what is now just the Calendar sheet in the original file with a new name, so you still have the original file should you need it.

I'm not sure how this will affect the Conditional Formatting, so give it a try and report back.

Hope this helps.

Pete

So that did work. However, like you suspected, the CF is a bit messed up.
Somehow it changed some of the colors, but when I try to change them back it says, "You may not use references to other workbooks for Conditional Formatting criteria."
So it kind of works.. I'll upload them.

In that case, you might have to arrange it so that you have three sheets in your original file - one for (just) your own data entry table, another for the expanded dates section (i.e. the coloured columns that I put in), and the calendar itself. Then you would be able to separate out the data entry sheet into a new file, as the rules governing the CF relate to areas of the expanded dates.

I'm going out soon, but I'll check back later to see how you get on.

Pete

Ah! That worked!!

Thank you so much.

I think this workbook is complete (for now, lol).

Well, that's good to hear - thanks for feeding back.

Pete