How to auto update a table when a change is happening to its source database

Hello Forum,

I have a simple question, with a not-so-simple answer as it seems. So i have a workbook with 2 sheets. The first one contains the database regarding personnel's travel expenses each month (Pic 1)Screenshot_1.png.

At the 2nd sheet i want to create a table that pulls data from the 1st one for for a later analysis based on three month periods (Q1, Q2, Q3) (Pic 2)Screenshot_2.png.

However the personnel that will be included in the database (1st sheet) will be changing constantly due to the fact that during a calendar year a couple of people will be recruited, while a few others will leave the company. Thus, i want the table at the 2nd sheet to be dynamic and auto-update when i am changing the values, or when rows are being deleted or inserted in the first sheet where the database is. For example, if i insert another employee in my database, i do not want to update my table at the 2nd sheet manually by inserting a row and the existing formulas copying formulas.

I tried this by creating a pivot table in conjunction with the offset formula, but the result was kind of clunky. I really do not know if what i want to do is plausible and whether it requires the use of pivot, formulas, vba or a combination of them.

Thanks in advance!

It might be simpler if the data was arranged differently.
In the attached the months in row 1 have been converted to dates and the range converted to a table.
Using Get & Transform the data below the dates is unpivoted and loaded as a table on Sheet3 using:

Please Login or Register  to view this content.

A column that calculates the quarter (Q) was added to the table and populated using: =ROUNDUP(MONTH([@Month])/3,0)
Table1_2 serves as the source data for the pivot table (also on Sheet3).
As Table1_2 is updated the pivot table will update (when refreshed).
Let us know if you have any questions.

Please Login or Register  to view this content.

Or mayby this formula will work if you put real dates in D1:O1

I don't think that I did a very good job of explaining. I am not so much recommending the use of Power Query, although it is available as a free add in for the 2010 version of Excel, as I was suggesting setting up the data in a row by row format. I simply used Power Query to get the data into that state. After making the conversion one time, I suggest discontinuing the use of the matrix table (sheet 1) adding data to the new table (sheet 3) of the file attached to my post. I believe that the longer you use the matrix style table on sheet 1 the harder it will be to work with.
I included the code in anticipation that the actual data table would contain more rows than that displayed on sheet 1. As long as that table is named Table 3 and the code is pasted into the advanced editor, it should convert the matrix table into a row by row table. If there are problems, you are welcome to upload your with names desensitized (you could later use find and replace to put them back) and I'll convert and send it back. Note that for .xlsx files the site limit is 1000 Kb, however .zip files may be considerably larger, although lately I have not been able to send very large .zip files for some reason.
As to the date format I am not sure why that should happen as no special formatting was applied so that it should display according to your computer's/system's settings.
Here is a link to a video presentation from one of the professors at Highline College concerning Power Query: https://www.youtube.com/watch?v=ohGFPF12Qwc&t=1275s
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.