Dynamic Pivot table

[amitdi]

I am looking to improve an Excel that I will share with my department. So I want to make this user-friendly and as much idiot-proof as I possibly can.

My current model (works but I am looking for a better model)

I have a time log sheet with following columns -
1) Date
2) Total Hours
3) Project A
4) Project B
5) Sick
6) Vacation Day

So employees will enter their hours across projects. When I release the sheet, (in the instruction email) I will ask every user to add columns where the header is their own projects. Different employees work on different projects. The total hours column will be a formula that sums all their hours. If the hours is anything other than 8, then there is conditional formatting to mark it in red.

There is another sheet where there is a pivot with dates grouped in months. The idea is for the employee to see their hours across projets.

Problem
As you may have guessed, everytime they add a project, they will have to add the project in the pivot.

What I am looking for
Is there anything I can do to improve the experience? Specifically, I want to automatically have the projects added to the pivot. I can have a 3rd master project list, then how do I make the entry from the list as a header. Employees can add and drop projects on adhoc basis. I can do some VBA too if there is a solution using VBA.

[Pepe Le Mokko]

Have a look at https://www.howtoexcel.org/pivot-tab...ps-and-tricks/