Morning,
I have a calendar type sheet to track people, and would like to know if it's possible to create a summary by date using a pivot table. Unfortunately I can't copy/paste from my computer or import VBA (it's an offline system) so I need the solution in writing - if there even is one. Thank you in advance.
The source data is a complicated sheet with multiple headers etc. Essentially you can pick one of several set activities (fixed by data validation) under the corresponding person (row) and date (column). The days are also formatted as dates. Filtering by title, dept etc is desired also. Count of blanks would help also.
e.g:
Would show in a pivot table with the 4th and Dept. filters selected as:
If there is no pivot table solution, I will just make a formula based fixed table.
I have a calendar type sheet to track people, and would like to know if it's possible to create a summary by date using a pivot table. Unfortunately I can't copy/paste from my computer or import VBA (it's an offline system) so I need the solution in writing - if there even is one. Thank you in advance.
The source data is a complicated sheet with multiple headers etc. Essentially you can pick one of several set activities (fixed by data validation) under the corresponding person (row) and date (column). The days are also formatted as dates. Filtering by title, dept etc is desired also. Count of blanks would help also.
e.g:
3-Aug-20 | 4-Aug-20 | 5-Aug-20 | ||||
Surname | Initials | Title | Dept. | Mon | Tues | Wed |
SMITH | A.B. | Mr | HR | Leave | Leave | |
SIMMONS | I.J. | Mr | HR | |||
JOHN | C.D. | Mrs | PROD | Training | Training | |
HOPKINS | G.G. | Mr | PROD | Training | ||
JONES | E.F. | Mr | IT | Conference | ||
HENRY | G.H. | Mr | IT |
Would show in a pivot table with the 4th and Dept. filters selected as:
HR | PROD | IT | Grand total | |
Leave | 1 | 1 | ||
Training | 2 | 2 | ||
Conference | 1 | 1 | ||
(Blank) | 1 | 1 | 2 | |
Sub Total | 2 | 2 | 2 | 6 |
If there is no pivot table solution, I will just make a formula based fixed table.