Events Calendar (tweak to existing template)

[ldybhw33]

Hi all

I am trying to put together a calendar for multiple people working on different projects.

Looking around the forum I found this template by Pete which is amazing and so, so close to exactly what I need -

excelforum.com/excel-programming-vba-macros/1228769-excel-calendar-to-track-events

Ideally, though, rather than show each listing as colour blocks on the calendar I would need the calendar entry to display the text from column B.

The formula at the moment is =IF(AND(I$5>=$D7,I$5<=$E7),"x","")

Thanks in advance for any help!

Helen x

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one and nobody wants to have to recreate your data from scratch.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired results are also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[ldybhw33]

Hi

I've attached a spreadsheet with Pete's original calendar and a mock up of what I'd like it to look like on the second sheet.

I hope that makes things clear.

H

[Pete_UK]

This was the original thread that the file was produced for:

https://www.excelforum.com/excel-pro...ck-events.html

(sorry about this, but the forum restraints mean that you are unable to post links until you have 10 posts).

I'll take a look at this after I've had something to eat.

Pete

[AliGW]

So, for example in FT9:

=IF(AND(FT$5>=$C9,FT$5<=$D9),$B9,"")

[Pete_UK]

Helen,

The colours in the original file (grey and yellow) were set by conditional formatting and depended on the number of participants which were in column F. You have deleted columns F and G, but the CF is still set up in the calendar part of the sheet, but of course does not work as the cells that they were looking at in column F have been removed. You should therefore remove the six CF conditions for those cells.

The original idea was to have one sheet which could list the events, and another sheet which displayed these in calendar format. In that original thread I suggested in Post #9 the approach of using colours in the calendar, rather than text values, so that the column widths could remain the same (narrow) and we could have the text on the left of the display (I had just copy/pasted the data for convenience, but the intention was to have formulae which brought the data across, so they could take account of the year selected. As you can see, though, the OP never got back to me after I posted the attachment). Doing what you have suggested in your file will mess this up, and you will have to manually adjust the column widths in order to be able to view any data displayed.

In your example, you have a duration of only one day for all the entries, and so the end-date column is redundant and could be removed. The formula in the main body of the calendar could then be modified to this in the current cell F7:

=IF(F$5=$C7,$B7,"")

and you can then copy this across and down as required. You will stall have the problem of having to modify the column widths in order to read the text.

Hope this helps.

Pete

[ldybhw33]

Thanks, both for the help.

H

[Pete_UK]

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