I would appreciate some assistance with a problem involving data that I have set up in a pivot table. This is what I am trying to do:
1. I start with a series of task codes and tabulate each day the time spent on each task (each day is a different column, with a column on the far RHS showing the total time per task which is updated each time the data is refreshed.
2. (Away from the pivot table, the single tasks are grouped and each task group has a predetermined maximum time allowed).
3. I would like to know at any time how much time is left for each task group
4. I have tried copying the data from the 'total time' column for each task and summing the relevant cells for the task group. However, the cells with no data (i.e, individual tasks in the group not undertaken during the time period, say month) show !REF and I am unable to perform a subtraction from the predetermined maximum to find out time remaining.
5. Any assistance would be very much appreciated as this exercise is beyond my limited Excel capabilities!