Spreadsheet: 39678d1237956794-auto-populating-excel-calendar-calendar-example-instructions
Sheet: Sheet1

The data table:
Notes:
The headers do not matter.  The dates need to be in column D.  You can have as many columns of data as you want.
The [NAME]:
For the [NAME] to work both workbooks have to be open.  If you can, it would simplify things a great deal if the data table was in the same workbook as the Calendar.
Cell AI3 is set up to show the filename of the workbook containing the data table from above.
The formulas reference cell AI2.  If the data table is saved under a different name, this calendar is set up so the value of AI3 would automatically be updated to the filename of the new workbook.  You would then just start typing ‘’[Waste... and by this time it should likely autofill the rest.  Do not forget the double apostrophe at the start of the cell, normally if you put one apostrophe in front of a value in a cell it just makes that value text.  By putting an apostrophe in front of the required apostrophe, you make it text but the required apostrophe stays.  If you were to put the data table in the same workbook, you would just need  to do a find/replace and replace all instances of
[Waste Log For Outlook.xls]
with nothing, just leave it blank.
To go the other way, just do a
find/replace and replace all
‘Data
With
‘[Waste Log For Outlook.xls]Data
You would of course put the actual name of the file though.
Now that you have the data set up right, you can now see how the calendar works.
If you click on the name of the month, a little dropdown box appears to the right.  Clicking on this gives all of the months as options.  The year can also be selected by clicking on the cell with the year in it.
A description of how some of the formulas work is at the end of this tutorial.
As you change the year and month, you should see the calendar update with the information from the table.
Example
Now if you want to set it up for different data, proceed as follows:
1.       Change your data table
2.       Sort by Date
3.       Rename the day headers
4.       Delete or add columns (if you have to add columns, copy the formula from cell C5 into added cells)
I had to change this to March to see the data, you can see the headers and the data don’t match up.
5.       Go to the bottom of the calendar and change the letters to match the columns the data is in.
Looking at the screen capture from step 2 it can be seen that the data is set up in the order of Product, Serial Number, Department, Date, Quantity so to correspond, C should be under Department, A should be under Product, B should be under Serial Number and E should be under Quantity.
6.       It should also be noted that if you delete columns the numbers in row 81 need to be sequential and repeating for each set of columns (i.e. columns C to F are 0 to -3, columns G to J are also 0 to -3 etc.
7.       If you likely only have 3 transactions per day, delete unnecessary rows.
8.       Enjoy it.
Explanation of formulas
Cell AF1 =MONTH("1 "&C1&B1) which means that the month number will be given based on the selections in B1 and C1.  Usually the month formula would take a date i.e. 1 July2009 and recognize it as a valid date and return the month of that date as a number.  So depending on the selection you make in the month dropdown the value in cell AF1 returns the month number.  This is required for the population of the date cells.
Cell AG11 is used to identify what the day of week the first day of the month is.  It is referenced in the day number cells [NAME], B3, [NAME], I3, etc.  The formula is =WEEKDAY(DATE(B1,AF1,1)), which is just returning the weekday number (i.e. 1 for Sunday, 2 for Monday, etc.) based on the selections of the year and month.
The day number cells for the first week determine which day is the first day and if it is Sunday, Sunday gets the one and the rest just add to it, if it is Monday, Sunday is blank, Monday gets the 1 and the rest add to Monday (i.e. Tuesday is 1+1, Wednesday is 2+1) the formula is =IF($AG$1=4,1,IF(I3="","",I3+1)) for example.   The last 8 days determine which is the last day of the month with the formula =IF(ISERROR(B55+1),"",IF(OR(DAY(DATE($B$1,$AF$1,B55+1))=1,B55=""),"",B55+1))
I’m not going to take too much time explaining the main formula as it is too long and convoluted.  Just know that it is dependent on the numbers in the Sunday column and the numbers and letters in the last couple of rows.  This is the formula though =IF(OR(OFFSET(J9,-$B9-1,J$81)="",ISERROR(MATCH(DATE($B$1,$AF$1,OFFSET(J9,-$B9-1,J$81)),OFFSET('[Waste Log For Outlook.xls]Data'!$D$2,0,0,COUNTA('[Waste Log For Outlook.xls]Data'!$D$2:$D$3813),1),0))),"",IF(INDIRECT($AI$2&"D"&MATCH(DATE($B$1,$AF$1,OFFSET(J9,-$B9-1,J$81)),OFFSET('[Waste Log For Outlook.xls]Data'!$D$2,0,0,COUNTA('[Waste Log For Outlook.xls]Data'!$D$2:$D$3813),1),0)+$B9)=DATE($B$1,$AF$1,OFFSET(J9,-$B9-1,J$81)),INDIRECT($AI$2&J$82&MATCH(DATE($B$1,$AF$1,OFFSET(J9,-$B9-1,J$81)),OFFSET('[Waste Log For Outlook.xls]Data'!$D$2,0,0,COUNTA('[Waste Log For Outlook.xls]Data'!$D$2:$D$3813),1),0)+$B9),""))
This colour indicates error handling
This colour indicates the determination of the dates that match the day the cell is in
The rest indicates which value is being pulled from the table.