Nov 08 2021 06:53 AM
I have a timesheet where every entry has a Category, and total number of hours spend on that task. It looks like this...
How can i create a query/summary on another sheet in my excel document that creates a summary showing the total number of hours contributed to each Category, and the total cost for each category. It would be good for this query/summary to be dynamic so if i add a entry on the timesheet this summary reflects that, such as new category names.
Sample timesheet has been attached.
Nov 08 2021 07:43 AM
One way to do this is a pivot table.
Nov 08 2021 07:44 AM
If you have Microsoft 365 or Office 2021, you can use dynamic array formulas:
Nov 08 2021 09:02 AM
The downside here is when i add a new row entry on the timehseet, the summary does not update to include the new entry. This is the part im trying to really have automated as this initial page will grow and it will be annoying to have to go and update the summary page manually every time a new entry/row is added.
Nov 08 2021 12:40 PM
A pivot table doesn't update itself automatically. The dynamic array formulas in my second sample workbook do.
The attached sample workbook (now a .xlsm) shows how to use code to update the pivot table automatically when the Summary sheet is activated.