Auto Updating Calendar with Info from Data Tab

Your common denominator would be the date.
For that a calendar with dates in the exact same format as the calendar with data could be added.
Working on an example to show that.
Can I adjust the structure of the data table. I would like to convert it into an Excel table for easier reference.

Will I be able to add multiple years on to the report?

That shouldn't be a problem. But let's first agree on the solution for the automatic update.

Here's the example.
Added a calendar with dates which are used for referencing.
Converted the data sheet to an Excel table. This also makes adding data easier as formulas and formatting are automatically copied to new rows.
Hope this is what you're looking for.

About adding multiple years to the report:
are you thinking of having a fixed set of multiple years on the report
or do you want to select a year
or a combination of the above options?

Here is the result for a report of 3 consecutive years.

You can:

• select any base year in B12. This will also select the next 3 years and collect their data from the data table.
• select a Test Data Group in A3 (it's a list). This is mainly done to avoid typos as this cell is used to determine the column of the data table to be used.
• copy the sheet if you want separate sheets for each test data group. Just change the name of the new sheet and select the appropriate test data group. No further adjustments needed.
• add data rows to the data table by either typing directly below the table or paste (preferably as text) data rows directly below the table. Formulas will be automatically copied to the new rows.
• add more years to the report. Just copy the columns AH:AV to the clipboard and paste them next to column AW.

Below some explaining of the used formulas. Other comments are in the workbook.

Formula for the collection of data from the data table:

Formula:

Please Login or Register  to view this content.

• Cell will be left blank if the formula can't find data and thus returns an #N/A! error.
• Cell will be left blank if the date is in the previous or next month
• The content of cell A3 determines which Test Group column is used to lookup data from
• Excel requires the #-character in the column name to be proceeded by an apostrophe. This is done in the variable DataColumn (see below).
• In order to be able to use a variable column name the reference is constructed with the INDIRECT-function.

DataColumn-formula:

Formula:

Please Login or Register  to view this content.

• This formula can be found in the Name Manager window (see tab Formulas in the ribbon).
• It has the worksheet as scope.
• When copying the worksheet, this variable is also copied with the new sheet as its scope.
• This variable is used to reduce complexity of the formual collecting the data.

Formula for the calendar (only first week of a month):

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

• This formula offsets Jan 1st (A14) of the base year (B12) to apply to the correct year

Formula:

Please Login or Register  to view this content.

• This formula calculates the number of the weekday

Formula:

Please Login or Register  to view this content.

• This formula calculates the daynumber of the date calculated by part 1.

By subtracting the result of Part 3 from the result of Part 2 an offset is calculated for the data calculated by Part 1

Good to hear that
Thx for the rep!