Forum Discussion
Automatically dating individual sheets within an Excel workbook
- We dont have a system that runs all of our reports the way we want them, so we break down what money is collected each day by how and what. It then gets totalled up for the month at the end.
I can relate, but putting all the transactions in one table instead with a date for each will be easier to deal with or manipulate with formulas.
as an example:
Here is a raw employee pay calculation dataset spanning 1/1/2020 thru 12/31/2021
if we followed your system of putting each employee in a sheet and having a sheet for each day so that would be 13 employees x time span of 1/1/2020 thru 12/31/2021 would be 4,383 sheets.
whereas in the above on 1 sheet for all 13 employees.
we could then transpose it into a usable formatted dataset:
That we can use in a Power Pivot:
which we can selectively expand to see the details of each year aggregation for example by quarters:
or by month:
and its dynamic (automatically update) the pivot table will automatically update as data is added just by refreshing the power query pressing ctrl+alt+F5, which should refresh the power query and the pivot table.
Data Model is setup this way:
https://youtu.be/e-CFYi52gpc
Here's the article link related to the file below:
https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_win10-mso_2016/pro-rate-salary-based-on-hire-and-fire-date-and/72f2a798-97da-46a4-bdf4-6aeccbe2e913?messageId=b88ac964-8fa7-455f-85c9-ea59b2cf390f&page=2
File: https://1drv.ms/x/s!AiUZUhiQtF3Fgt8Yh-xP2VBUiYJBiw?e=z8uU0s
