Excel Calendar to track events

Asking to see what is the art of the possible here.

We currently use an event tracker in an excel sheet. This is just a hand-made calendar for the year (days are rows) and the event location down the side.
For specific dates, we type in a cell, the company name, how many people are attending and some misc info. This is then made into a merged cell and coloured specific to the company.
This works great and allows us to print off a year (monthly calendars wouldn't work for us).

The issue comes when we try to extract data from it. If I want to know how many people from company X attended various events over a period, I can't. I have to manually look at each cell for that company and write down the number of people we have on record attending.

I did see this leave tracker which gave me hope.

End game - There would be a tab where I could enter the raw data. Company name, how many people, start date and end date etc.
Then it could take this information and populate another tab which contains the calendar. Putting that company in the right place with the right colour and shows briefly how many people are attending. Any changes made to the details tab would reflect in the calendar.

This way I still get the printable look of a calendar but have the details tab so I can extract data. Best of both worlds.
Unable to produce a dummy or you look at right now, but if required I can try to make something. Just hoping for some initial thoughts.

All help is very much appreciated here.

I have submitted several calendar files on this forum, and I am sure I can amend one to suit your requirements.

Please attach a sample Excel workbook and I can adjust that.

Hope this helps.

Pete

Pete,

Thanks for taking a look.

I've attached a sample one. This one will do down for more rows.
The only curveball would be that the rows are broken up by area too. North, south etc. This may pose an issue.

I won't be able to modify directly the file you have attached, as I expected to see a list of events that are then transcribed onto the yearly calendar. Having read your post again, I realise that that is what you want to achieve (!)

I'll have a look through my calendar files and find one which is similar to that.

Pete

Looking at your file, I have deduced that the following columns are needed in the Events sheet (where you list all the events):

Region
Customer
Locaton
Start Date
End Date
Numbers
Bid_num
Submitted

Please confirm that this list would be sufficient before I do too much work on it.

Pete

Pete,

Again thank you for helping me with this.
Looking at it I would say another entry of "RPOC" would be great. This would go on the calendar part at the very start, before company.
So I'm guessing it would look like:

RPOC - Customer - Numbers - Bid No

The other details would of course remain in the data tab for exploitation.
However, if what I have just said is confusing, then yes, carry on as you planned to!

Okay, I can include that. Can you explain the significance of the colours?

Also, it will be difficult to merge the cells (well, impossible using formulae), so I'll think about ways of achieving that effect.

Pete

Thanks Pete.

Just want to clarify a few things to save you doing any additional work.

The colours are just for a visual flair for yearly print-offs. So, each company would have it's own colour. So we can see from a glance how many from X are going to Y.
Merged cells are not required. It was just done because the people who originally made this spread, didn't know you could ctrl+enter to enter a new line in a cell. So they made the cells like they did to show all the data.

The main reason for this is so we can make a report using the raw data. For example, During April-July, X companies visited Y Area and Z people attended.

I've been working on the basis of listing all your events in one sheet and then having them displayed (automatically) on another sheet in your yearly calendar format.

I had in mind a Gantt chart type display, so there would only be coloured bars indicating the date range. Thus, the details for that bar would then appear on the left side of the sheet (this area can be fixed, and you can scroll across the calendar display). This gives rise to a number of problems, though. On your example file, the customer, WATHGILL for example, has 4 events shown in the period, one after the other, and the first client OTTE has 5 events and some of these overlap in time. However, all of these would have to be shown on separate rows in order to have the details listed on the left. In a year there may be several extra rows required for each client, so the display would then become a lot longer than you have in this example.

I could provide drop-downs whereby you could select the Region on one, and the client on another (and the year on yet another one, thinking ahead), so that could reduce the "depth" of the sheet, but it is still not the same display as you are used to.

What do you think about this type of display?

Pete

Pete,

Your idea sounds interesting. There isn't a main focus on the visuals as long as a yearly print off can be made.
I know I can do most of this in SharePoint, the issue is there is having a yearly calendar view (SharePoint only has monthly views).
I am unsure why there are multiple events over a course of some days. I would need to speak to the person who organised it, but it doesn't make sense to me. I would ignore it. Maybe it was an error in the spreadsheet on creation.

The grey/blue boxes on the sheet are for events where there is no idea how many people will attend until the actual day, that is why no people are mentioned. I guess in this case, if people attending = 0, nothing is put in for how many attended.
I've attached another sheet where I removed the double entries and a second tab to show the data sheet.

I've set this up in the attached file, where I have added another sheet, Calendar, to demonstrate what I had in mind.

You can select the year of interest using the drop-down in B1 - the calendar automatically adjusts, and takes account of leap years (try 2020).

For the moment I have just copied your data into the columns on the left hand side, although I would envisage having formulae to bring across only the relevant records, dependent on the drop-down choice(s). It would be possible to have a drop-down to select the Region (or All), and only those records would be displayed for the given year.

Anyway, using your data, which is largely to do with July and August, you will need to scroll across to see the bars, which are drawn using two colours (depending on the number of attendees). Is this the kind of thing that you wanted?

Hope this helps.

Pete