Trying to make excel generate a dynamic calendar grid based off a list of events

[DrFistington]

I was looking for advice on the best way to generate a calendar dynamically in Excel that includes information entered on another worksheet. Here's the overall premise of what I'm trying to do: I want staff in a medical office to be able to enter 2 columns of information into a row on a worksheet. Column A will be a date, and column B will be text describing what will happen on that date, for example:

Date	Description
12/5/2019	2:00 pm - Lab Visit: CBC, CMP, MG, and ABG
12/10/2019	11:00 am - Chemotherapy Visit

Once they've entered that information on one worksheet I'd like for them to be able to use a Macro (or any other viable method) to generate a table that looks like a standard paper calendar (7 Columns by 5 Rows for each month). The generated table should be for the next 60 days, and the information entered in the table should automatically fall into the cell for the corresponding date in the calendar grid. The goal is to be able to generate a personalized calendar for each patient that shows their upcoming treatment dates along with their appointment times and what will be done during each appointment.

Any advice would be greatly appreciated. Thanks!

 

[Perpa]

DrFistington,
Beginning on sheet2, Post# 17 of the following thread:

Excel's Magic Calendar

Excel's Magic Calendar

For over a decade I've missed two things in Excel. (Okay, I've really missed more than two, but this post is about the two that are on my mind at this moment.) One, a ruler like in Word and PowerPoint. And two, a Make Calendar button that lets you select a list of events, dates in the one...

www.mrexcel.com

Shows a calendar that automatically loads the list in the same format you have shown ( 'Date' and 'Description'). You can see an example calendar on sheet3, Post #24.
However, the list of Dates and Descriptions could exist on another sheet.

As it is shown, the list is on the same sheet as the calendar, but the list does not print, just the calendar with the Descriptions in the dates of the selected month (cell J4).
You can put the list for both months in one list, then print each month separately by selecting the month in cell J4. You will need to change the year manually in cell H4.
I hope this is helpful.
Perpa

 

[RasGhul]

Hi Dr,

Here's mock up version of what you've described. See if this suits;

Dr Task Patient Calendar.xlsx

 

[DrFistington]

Hi Dr,

Here's mock up version of what you've described. See if this suits;

Dr Task Patient Calendar.xlsx

Hell yeah! Thank you so much, this is exactly what I'm looking for. I just had a few questions:
1. Will this continue to work for future months (for example, if the dates spanned from april 2020 into may 2020, would the calendar grids still generate correctly?)
2. Should I be able to format the second sheet so that the months are listed vertically instead of horizontally? So that the entire month of november is shown, and then if you scroll down, underneath that, the entire month of december will show, etc? I'm going to play around with it now, so if I'm able to figure that out I'll post a reply.

 

[DrFistington]

Ok, so I was able to answer a few of the questions I had. It was just a quick cut and paste job to change the calendars so that they are listed vertically rather than horizontally. Also, as long as I adjust the Date field on sheet2 it will update the months being displayed. I should be able to pretty easily make a formula to look at the earliest date listed in the table in sheet 1, and then set the date field on sheet 2 equal to that so that the months being displayed will update automatically. This is awesome and will help save hours of busy work that we have nurses doing every day. Thank you!

 

[RasGhul]

Sounds great, I wasnt sure whether you wanted to control the months or have them update automatically.

If you always want to see this month & next in the Date field just enter the following formula;

=today()

Note that you can change Patient name to extract those appointments to the dynamic calendar.

I'll update my sample version to be vertical and send you a link.