Autopopulate calendar from data

I'm trying to get data from our catering/event management software to be viewed as a calendar. It would be similar to the one in a thread from yesterday but with more information on each line, such as the group size and the time. I've searched around but couldn't find anything.

Hi Bryan,

(No offence was taken for your previous post).

Can you confirm which columns of data you want to display on the calendar? Is it columns D, F and J ?

Some of your titles seem a bit long, and so they may appear cramped on the display.

I'll be going out soon, but I can pick this up when I return later.

Hope this helps.

Pete

Thanks Pete!

The columns I'm looking to capture in the calendar are B, F, I, and J, but not necessarily in that order. The data is being exported directly in that order from our event management software. Ideally it would be in order of importance (J, F, I, B) with the final one potentially ignored altogether since it tends to be lengthy and often the least important detail.

Hi Bryan,

sorry for the slight delay in getting back to you - I have not been able to get onto the site for several hours today.

I've incorporated your data into one of my calendar files, so see if this does what you are hoping for.

In the bookings sheet I have used 3 columns of formulae (A, B and C), which are copied down to row 1000. If your data table exceeds this, then you will need to copy the 3 formulae down further. These formulae transform the data so that it can be used by the Calendar sheet, but you might need to adjust the formula in column C, as this is still too big to fit in the calendar boxes. I've use the LEFT function to truncate the text, so you might need to reduce the number of characters further (or use an alias for the locations and have an abbreviated event title).

The Calendar sheet is fairly obvious to use - there are drop-downs in cells K5 and K6 (coloured yellow, for clarity), and with these you can control which month and year you want to view. The data from the bookings sheet can flow from one year to the next, so you don't need to have a file just for one year - just keep accumulating your data. Also, the bookings data can be entered in any order, so it doesn't need to be sorted chronologically - just add new bookings to the bottom of the table.

There is also another drop-down in cell K10, which controls what you can see. Only 10 bookings are shown for any particular day, but you can select the range in increments of 10 if you exceed this number.

Anyway, play about with it and let me know what you think.

Hope this helps.

Pete

I've been out the last few days so apologies for not jumping on this. It looks great! I love the ability to control how many events will show per day. I'm going to jump in and start playing around now but wanted to say thank you first!

Well Bryan, I hope you have fun playing with it. As I said, you might need to adjust the formula in column C of the Bookings sheet, as that controls what is actually displayed in the Calendar. I should have also pointed out that you can hide columns A to D of the Bookings sheet, so that it looks the same as your initial table.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete