linking a calendar to a sheet

Hi all , please can you help me, I have a calendar that I have done in excel and it has a sheet attached to it. what I want to do is if on the sheet I change the date it will then move it automatically on the calendar. please advise if this is possible. thanking you

Yes, that can be done. Here's a few links to similar threads (all slightly different) that I have responded to recently:

http://www.excelforum.com/excel-form...m-a-table.html

http://www.excelforum.com/excel-gene...-calendar.html

http://www.excelforum.com/excel-form...vot-table.html

http://www.excelforum.com/excel-gene...te-ranges.html

http://www.excelforum.com/excel-form...-and-date.html

If you need further help then attach a sample workbook so I can see how it can be adapted.

Hope this helps.

Pete

Hi Pete,

I have tried and still don't know how to do it, i have attached the file.
On sheet 1 you will see the dates and description of the event. then on the calendar is the
event name, i need to know how to when i change the date on the sheet it changes it on the calendar with the event description.

thank you
Event and Marketing V1.xlsx

You need to make the calendar sheets much more dynamic. At the moment you just have a simple link back to the Overview sheet (e.g. =Overview!C7), which doesn't take any account of the date on which that activity is scheduled to take place. So, if you change the date in the Overview sheet it will not affect that formula.

If you look at any of my calendars you will see that I only have one sheet for the month, rather than the 12 that you have, and you can select the month and year using drop-downs. In addition to that, there is also at least one helper column in the Activity sheet which generates a relationship with the date - the calendar sheet can then dynamically pick up the events according to that date.

When I have amended a calendar for a poster I have generally adjusted the Activities sheet so that it matches the OP's file, and then it is quite easy to copy the data across so that it relates to the OP's requirements. I would suggest that you take a detailed look at the fourth link that I gave you above, as that copes with ranges of dates as well as individual dates.

Hope this helps.

Pete

Hi Nicole,

I've amended the calendar file that I suggested earlier and incorporated your data in it, so now there is only one monthly calendar but you can select the month and year using the drop-downs in K2 and K3. Everything is now linked to the dates in the Overview sheet, so if you change anything there it should automatically get reflected through. I have two columns for the dates (start and end) so that you can specify a date-range for an activity. Alternatively, if the event is just for a single day, you can leave the end date blank.

I have started to put the other events at the bottom of your table, so I hope you can carry on with those from the months after April.

You should not need to amend any of the formulae in blue to the right of your data, except for perhaps copying those in columns O and Q down further to accommodate more entries. In the calendar sheet you should not need to change anything (apart from the drop-downs). You might like to set up some conditional formatting to give you the colours that you set up manually in the other calendar.

Hope this helps.

Pete

Hi Nicole,

thanks for the rep and for marking the thread as Solved, as per my PM. I forgot to mention that if you want the file to look more like your own file (by having a separate sheet for each month), then you can just copy the Calendar sheet 11 times and rename the sheets with the month name, Jan, Feb, March etc. In this case you don't really need the data validation drop-down in cell K2 - you can change this in each sheet to the numbers 1, 2, 3 etc. as appropriate for each month. It's probably still worthwhile to retain the year drop-down in K3, so that you could have a calendar from April this year to March next year, for example, but if you only want it for a particular calendar year then it would be more convenient to use, say, the January sheet to select the year, so in all other sheets you can just have a simple link to that sheet:

=Jan!K3

then all the months will use the same year as that chosen in the Jan sheet.

Anyway, hope this helps.

Pete