Quarterly activity calendar in Excel

Dear team,

I need your help in creating quarterly activity calendar. In my organization, we will conduct nutritional camps every quarter in multiple places, my manager asked me to prepare a calendar in excel which will help the team to plan their camps accordingly. If one camp conducted in one place on someday after three months the same camp has to be conducted there. Now I have created the same in the excel manually I have to update which place is conducted (highlighted the cell in green color) and after three months where they have to conduct (highlighted the cell in yellow color).

I have attached one sample calendar which I prepared, In my excel in third row there are dropdowns for to select District, Month and Year. based on the data provided in Sheet1- 'Data', I have update the data in Sheet2-'Calendar'.

Example:
In Anantapur district 4 camps were conducted 5th Jan 2023 one camp conducted in Rayadurg place and after three months again one more camp conducted on 10th April 2023, I have highlighted those 2 cells with green color. again they need to conducted the camp in the month of July 2023, but till now they didn't conducted so its showing in Yellow color, if they didn't conduct any camp till 31st July, the yellow color need to change with red color. Based on the data provided in Data sheet the in the Calendar the dates need to be highlighted in and the place of the camp need to be displayed. Like this I have to work for 13 districts data.

I dont know whether this can be automated using VBA or excel formulas. Please help me to prepare the calendar.

Thanks in advance

This could be done with a few formula - in the Data sheet you can set up a unique reference for each record and then in the Calendar sheet you can use INDEX and MATCH to retrieve the appropriate record. Conditional formatting can be used to generate the colours automatically for repeat camps.

I'm not sure why you need drop-downs for the month and year, as your layout seems to be fixed January to December for the same year. If you want to vary the starting month, then the other months (and year) should then generate automatically from that starting month, although you would then need other formulae which generate the dates in the calendar automatically.

Similarly, I'm not sure why you would need more than one drop-down for the location - surely, you would want to see what has been arranged for a particular location (?)

Hope this helps.

Pete

Hi Pete,

Thanks for your quick response, I think I couldn't communicate properly. Why I need dropdown for month and year is, we will continue this activity next years also that's why I have given dropdowns for Month & Year. Based on the data available in the Data sheet I wanted to see each district when and where they planned Camps and when will be the next camps. When I selected the district name from the 'District' drop down, the calendar has to show only that particular district camp details only. I want all this process automated. hence I requested your help to automate. Is it possible to automate or not based on the data in Data sheet. Now whatever I have taken month wise dates is entered manually, it will be difficult if I will do this for 3 to 4 years as I have to adjust days for all the months.

Thanks & Regards

Hi,

can anyone help me with my request. Please tell me whether it is possible to create a calendar as per my request or not

Thanks & Regards

I am closing this thread as I am feeling like I didn't explain properly. I am opening a new request. Thank you