Sep 28 2021 12:54 PM
Sep 28 2021 12:54 PM
I have a daily spreadsheet set up for each day of the month. There are 31 sheets in this workbook. A new workbook is downloaded from a master copy every month. I am trying to set it up so that you can type the date in the first sheet of the undated workbook and it automatically fills in the dates for the rest of the month. I have several individual businesses using this form and am trying to make it easy for them to use. Dating the sheets correctly can be a persistent issue. I am currently using the formula:
**each sheet is dated at the tab as well**
The issue is, if there aren't 31 days in the month, the final sheet that sums up all of the activity for the rest of the month is dated for the following month. For example: My September spreadsheet is dated October 1st on the final sheet. It's a problem because I have accountants using this and the date being incorrect could be an issue. I know that I can remove the formula from the last day, but am trying to avoid that. Even an error message would be better.
Sep 29 2021 07:13 AM
Sep 29 2021 11:04 AM - edited Sep 29 2021 11:14 AM
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:
Here's the article link related to the file below: