Calendar with Master that can be filtered - help needed please?

Hi all,

Firstly, thank you for letting me join, I am hoping to learn SO much from you all

I think I have followed the instructions correctly to upload a vague idea of what I'm trying to create!

I work in an academic setting and I have been asked to created a Excel spreadsheet that shows a Master calendar that can be filtered; it needs to take its data from a number of worksheet sources (all in the same spreadsheet) and needs to be filtered to show, for example a particular module start and end dates, all module start and end dates for a particular course, course dates and college term dates and then (hopefully) the option to clear the filters and show all.

I have only ever done fairly basic spreadsheets before for stuff like data comparison, so I'm pretty lost. I have done a little bit of work with pivot tables, but I am not sure if that's what I would need to use here?

I am new to the job and there doesn't seem to be anyone I can ask for help. I just really need the bare bones and then I can add all of the data and recreate it for each academic year moving forward. I would be very grateful for any help and instructions on how to do this please?

Thanks SO much
Cathy

Hi Bernie,

Thank you so much for the suggestion. I will go back to my manager and she what she says, although I suggested similar when I was first asked about this and she seems very keen to have a 'proper' calendar view and then lots of buttons to click to filter and turn data on and off. Sigh....

The calendar view is the big problem - if it was a big sheet, then I could do separate tabs that use Pivot Tables to filter it down to what is wanted, but she wants to have the option of a master 'Super Calendar' that shows everything and that is the bit I just can't get my head around!

Thanks loads,
Cathy

Thanks very much Bernie, I will have a play about, adding data and seeing how it responds.

Thanks again,
Cathy

Hi Bernie, me again....

I am SO sorry, I am being totally dopey here but I just don't understand how the calendar draft 3 spreadsheet works!! Attachment 729147

I think I have just attached a screenshot - I get that in the database sheet there is the dropdown menu and the user can choose the type of event, then add the name and the start and end dates - that bit my poor old brain gets!

I can see that if I put a date value (e.g. 01/04/2021) into the blue box on either of the next tabs then it cleverly knows which day of the week that is, but I don't understand what the yellow boxes are that have the formula:

=IF(WEEKDAY(DATEVALUE(A2&" 1, "&A1))=1,DATEVALUE(A2&" 1, "&A1),DATEVALUE(A2&" 1, "&A1)-WEEKDAY(DATEVALUE(A2&" 1, "&A1)-1))

I'm also stuck about the orange boxes that have the formula:

=IFERROR(INDEX(DataBase!$B:$B,SMALL(IF(DataBase!$C$1:$C$10000<=INDEX(A:A,INT(ROW()/4)*4),IF(DataBase!$D$1:$D$10000>=INDEX(A:A,INT(ROW()/4)*4),IF(DataBase!$A$1:$A$10000=$C$1,ROW(DataBase!$C$1:$C$10000)))),MOD(ROW(),4))),"")

I am guessing that they search the DataBase sheet and if they pick up a match then they display the event that matches with the date?

What I don't get (I feel really thick here, SORRY) is why I can't see any events - you got me started with some great event examples and dates (using the Uk date format too, thank you!), but how do I get them to appear on the actual calendar display sheet?

Thanks SO much for your help - you are an Excel genius!!
Cathy

Hi Bernie,

You're going to wish that you had never started helping me!!!!

I can see that on the DataBase sheet there are examples of April events:

Course Chemistry Module 2 04/04/2021 04/04/2021
Course Chemistry Module 3 13/04/2021 13/04/2021
Course Chemistry Module 4 08/04/2021 08/04/2021
Course Chemistry Module 5 15/04/2021 15/04/2021
Course Chemistry Module 6 22/04/2021 22/04/2021

So when I go to the 3 Events Per Monthly View sheet I can see that it says the year is 2021 and the month is April and I've chosen 'Courses' from the C1 dropdown, but nothing happens - Just #Value! in each of the blue boxes, #Value! in each of the yellow boxes and nothing in any of the orange boxes. I've added another screenshot.Attachment 729167

I haven't changed anything in either of the calendar sheet views - other than to filter the value in C1 to Courses or College Term Dates, just to test as you suggested. I have tried adding three new events to the bottom of the DataBase sheet, under Type 'Module' but, again, when I try filtering to 'Module' on the calendar sheet view nothing happens.

I must be doing something wrong - any ideas?!

Thanks SO much,
Cathy