I'm looking for a way to add a multi area calendar to an excel sheet

[s2h]

I have Multiple work areas that I have to schedule various shows in
and I would like to create an excel sheet that automatically draws the scheduled shows into a chart

basically Y axis will be the Days of the month
and X axis will be the different work areas

populated in the chart will be the name of the show along with the order# and I want it to draw a bar/box that fills in the start/end dates



attached is an example of what I am trying to make it look like along with some sample data

Is something like this possible?

[Pete_UK]

Yes, it is possible. I'll try and get this set up for you before I go to bed, and post back. I presume you would like to be able to select the year and month, and for the display to automatically adjust.

Pete

[s2h]

Yes, it is possible. I'll try and get this set up for you before I go to bed, and post back. I presume you would like to be able to select the year and month, and for the display to automatically adjust.

Pete

that would be awesome

I'd love to be able to have one page with the chart, and a 2nd page with the Data

in the end, its going to be 15 areas total, but I can start with the 4 and expand from there
and the date in the Y axis should be dictated by a start and stop date selected by the end user

is it possible to make it show the blocked out show area even if the start or stop date is in the middle of the days???
meaning I have selected a date of 10/4, but a show started on 10/2 and ends on 10/9.....can I still show the existing 4-9th days in that chart?
or will I always have to chose a start date before the earliest show I want to see

[Pete_UK]

Ah well, I based this on what I could deduce from your picture and file attachment, so everything is in one sheet, though that is relatively easy change. I assumed that you wanted a monthly calendar, so I have given you the chance to change the year and month in the yellow cells on row 1. Your data table is now in columns G to K, and I've added some other test data (in red) to that area - you can just keep adding more stuff as you need.

I have some formulae in columns A to E which take your date ranges and expand them into individual dates for each show. This is all automatic, but you can see that the 6 example data entries expand down to row 38, so it is important that these formulae are copied down far enough to cover your future data requirements - I've only copied down to row 100, but you could copy these to row 5000 (or beyond), and even hide them from view.

I've used blue for the third area, and you can see that my last example data spans across a month - just change P1 to 11 to see what happens to the display.

Hope this helps.

Pete

[s2h]

Wow.. that is great

I can do the cut/paste/add more stuff now that I see exactly what to do to make it happen


This will be awesome


Thank you very much for your help

[Pete_UK]

You're welcome - thanks for the rep.

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 the thread as SOLVED.

Pete

[s2h]

the only problem I'm having is the items I have, contain more than 1 line of text, and the cells dont resize to match the text space needed
not sure if theres a way to make it so that it auto fixes every time a new item is added or changed

Right now I can manually select all cells, and then hit the wrap text button off and back on and everything auto shifts.
but I'd like for that to be automated somehow...

[Pete_UK]

Excel is not very good at re-sizing text areas, as it is not a word processor. I'm afraid you are stuck with adjusting row height manually, as you describe.

Pete