Filtering an excel calendar - multiple entires per cell??

[ljw89]

Hi All,

I'm hoping someone can help me with this. I am by no means an excel expert. I am trying to create a calendar which will display multiple people's diaries, but will essentially allow me to filter by person and by month. (for example if I wanted to see what Jill and Tom were doing in April, I could select their names from some sort of drop down list, and then be able to see what they were doing in April (or across the whole year). In theory that does not sound too complicated but I am struggling!!

I have tried to attribute a colour to each person, and then just filter each month by colour, but where there are 2 entries per date (for 2 different people), I am not sure how to filter this (As excel obviously only picks up one colour). I was wondering if there was a way to create a legend at the side of the calendar, and then I could perhaps pick from a drop down list, and could then compare calendars?

Or if anyone has any other ideas as to how I might go about this, that would be very helpful!

Thanks in advance for your help.

Lucy

[ljw89]

Basically I have 12 peoples calendars that I would like to be able to compare on one sheet. (and filter by person) is this possible?

any advice would be much appreciated!

[MickG]

If you allocated each person a sheet with dates in column "A" and Related information to the date in columns to the right of the date then, with a master sheet you could call up the person and the month or date to see the Activities for the people selected on that date/Month. Is that what you have in mnd.??

[ljw89]

Thanks Mick. It's a hard one. bascally the information is related to 24 different artists. and I want to be able to view their media coverage/planned events on one calendar. I had set up a calendar and colour coded each artist, but I was unable to filter this easily. It also meant that if 2 artists had an event on the same day, the colour filter would not work.

If I do it the way that you are suggesting, would I be able to view multiple artist calendars on one master calendar?

How would I go about creating the master calendar?

Thanks for your help.

Lucy

[ljw89]

Hi again,

I think I know what you mean. I've attached a template. The first sheet shows the master calendar that I would like all information to be displayed on. I have created 2 sheets as a test (Malcolm and Kate's calendars).

I know that I would probably have to write a macro to get the events placed on each sheet to appear in the master calendar - would anyone be able to help me do this? And would I be able to colour code them? So for example if Malcolm's events appeared on the calendar as purple, and KAte's appeared as orange?

Then what I would need to be able to do is filter out the different calendars from the master calendar. (as there are going to be more than 2 people's calendars showing up on the master). So would I be able to set up a filter so that the master calendar would only show me malcolms events if necessary, or compare more than 2 calendars together?

I know it's a lot to ask but any advice on how to do this would be much appreciated!!

THank you!!

[MickG]

I've seen your file and understand what you basicaly want.
I have produced an basic example sheet, althought different from your sheet, there are aspect of both that could be amalgamated.
Have a look at the example & tell me what you would like to end up with.
If you use the Userform to select month, the Master sheet could accept a number of columns for each "Artist".
You can have them verically or as in the example in columns for each "Artist".
Let me know how you would like to see the result.
Regrds Mick

[ljw89]

THanks for this Mick. ideally, the master sheet would look like a calendar. i did consider having different columns for each artist, but there are 24 in total so it would be quite a large worksheet. if there was a way that the master sheet could resemble a simple calendar, that would perhaps be better. I don't know if the macro is working in the spreadsheet that you sent me. (perhaps as i am using a mac as opposed to Windows).

If there was a way that the 'update calendar' button on each tab would update individual calendar information on the master calendar (with a colour coded for each artist) then I could perhaps just filter the overall calendar by colour. is there a way of doing that?

Thanks again for all your help!

Lucy

[MickG]

If you arre going to use some sort of coding its quite important that you know if the userform showed when you clicked the Button.
Could you let me know.

[ljw89]

HI Mick,

have tried it on a windows pc and it worked. Would there be a way to retrieve the data in a similar way, but display it on the calendar? And would it be possible to retrieve 2 artists calendars at once?

thanks again!

[ljw89]

Hi Mick

Sorry i can see now on your example that you can view multiple calendars. I just wondered if these could be displayd on a calender as opposed to in columns?

[ljw89]

I have attached an example that I have found elsewhere on the internet. Would it be possible to use a similar template as in this example - adding all information in to one extra sheet, but then display the colour coded results on the master calendar in my original template ( as opposed to the format here).

Then if I could create a macro similar to the one used in your sheet, which would allow me to view 2 artists' calendars on the master calendar at once? Would that be possible?

[MickG]

I suppose it depends on how much data you have to display,
At the moment it displays a specific Month but it could display a single day or number of days.
If you have a limited amount of data that would be displayed , you could have your calendar as per your file and when you clicked a date, and select one or more artists, a userform could show relating to that date and the Information for those artists.
It really about what works best with you situation.

[ljw89]

There is not loads of data to display. but some artists do have events on the same day. it would be more useful to filter data by month rather than specific ddays. is there a way to adept the example i attached in my last message, but then add a macro to the master sheet to filter each artist as you have done previously?

[ljw89]

Mick,

is there a way I could record a macro to do a similar thing that is happening in the Calendar_Solution example? i cannot figure out how to record it. If i could turn the 'Update calendar' in to a button (in the attached), so that each entry will appear as a different colour (relating to a specific artist). Could you tell me how I would go about this? (preferably by recording as opposed to writing as I am not familiar with coding).

Thanks again!

[MickG]

Unfortunately , recording a Macro will only produce what you physically do on the sheet, It cannot provide a logical solution to manipulate your data.
If you want to know what a person is doing on a particulsr month, my code will do that, you can colour the individual results how you like within the code, and set the results as you wish vertically , horizontally, whatever !!!.

I can't see the point of having a Calendar to take up the whole sheet when your Results may only be 2 or 3 cells with very limited room to get the information in.
Why do you want a button on each sheet. Is the purpose to see what one person is doing and at the same time to see if it clashed with someonelse appointments, other than that , I would think doing everything from the master sheet would be best
I don't mind altering my code to achieve the best result, but it needs some thought as to how best you can achieve this, !!!!

[ljw89]

Hi Mick

Having discussed it with a colleague, the main idea would be that on the calendar, you could filter by person and see only their calendar. Would it be possible to keep all the data in one table (on another sheet- as in the calendar solution example), and set a colour to each artist. Then when you click the update calendar button, it updates on the master calendar.

Then if there could be a drop down menu of names on the master sheet, and when you select a name, the master calendar only displays one persons calendar.

Is that possible? If I could figure out how to achieve this (similar to calendar solutions example) then I don't think it would matter so much about being able to see 2 people's calendars side by side.

Any help you can offer on how to achieve that would be greatly appreciated.

Thanks for your patience!

[ljw89]

Hi All,

Any help anyone can offer would be much appreciated!

[MickG]

Unfortunately this is going nowhere fast.
Yesterday I asked some Questions, none of which you have answered, yet below is your Quote which is in effect what I have alresdy given you.

Then if there could be a drop down menu of names on the master sheet, and when you select a name, the master calendar only displays one persons calendar.

If you can't go with some modification of this code , I'm afraid I'll have to let someone else have a go .
Regrds Mick