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.

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.

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?

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).