My boss’s boss wanted me to make a big old spreadsheet with a tab for 30 different people where managers of the different departments can put the hours for the people. Then a chart would be made that could show the hours for everyone, or it could be broken down into the hours for just the engineers, technicians, etc. I was having a lot of trouble getting it working, and I eventually got something though it’s maybe not the best way to do it.
So although my spreadsheet works, it might have issues in the future, so big boss wants me to change the spreadsheet. He wants it so that there is a pivot table for each separate person, and then a master pivot table that takes the data from the other pivot tables, and then the chart is made from that master pivot table. Is this possible? And if so, how? I know you can make a pivot table from multiple data sources, but I’m having trouble with it, and I can’t tell if you can make it from multiple pivot tables as sources. Or if you maybe theoretically can but in actuality it is likely to get screwed up.
TLDR: is it possible in Microsoft Excel to make a pivot table where the source data is other pivot tables?
A pivot table is a summary report based upon data from a range elsewhere in the workbook. A pivot chart is the same idea, but represented graphically. They do not really store data, they report it.
I would store all the numbers in one place, and have one pivot table report on everything. This is the ideal solution. The pages of the pivot table could be departments. To see an individual’s hours, use the PT filter.
If your boss wants to see multiple reports at the same time, then you are forced to have multiple PT’s.
That was my solution, I had a tab for each of the 30 people, then one Total Hours tab that had all the data summarized, and a pivot table made from all that data, and then a chart that was made from the pivot table. It seems to work so far.
My boss’s issue with that is that the projects to keep track of will keep growing. So right now engineer Joe has 10 projects, but as he finishes these and takes on new ones, by next year he could have 60 projects on his tab. In order to deal with this, I have the Total Hours tab reference rows 2-100 instead of just 2-11, so that as new projects get added, the Total Hours tab will reference them, and the chart will update accordingly. And once Joe has more than 100 projects on his list, I can update the Total Hours tab to reference rows 2-200 or whatever. Not pretty or ideal, but it seems workable.
But while I was finishing my spreadsheet, my boss was asking someone he worked with before on how best to do this, and they said the best way would be to make 30 pivot tables, and then a master pivot table that summarized those 30 pivot tables, and then a chart that was made from the master pivot table. This way when Joe gets project # 11, his manager will add project 11 to Joe’s tab, and Joe’s pivot table will automatically update, which will update the master pivot table, which will update the chart. It makes sense in theory but I’m just not sure how to do it or if it is actually the best way to do it.