Pivot table question

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!

Would need a sample of your data to confirm this

But I believe the problem in your formula could be got around by using an IF statement, and then either the ISERR command or the ISBLANK command to substitue blanks/#ref with zeros

eg if(isblank(a1),0,a1)+if(isblank(b1),0,b1)

Hope this makes sense, if not post a small example and I'll show you in the sheet

Thanks Waylander. It's early hours in the am here in Oz, and I have the data on my work computer. Will post tomorrow from there.

Cheers
Seagull6

Thanks again Waylander
Here is some data that I am trying to work on for this current month. Have included a couple of explanatory notes.

But, basically it is a system of recording data from a timesheet and then collating the data according to task and then to use the summary of that data to generate an invoice.

As I said, my Excel capabilitites are somewhat limited, and this is the way I have set it up, but am totally open to any other suggestions from the group.

Many, many thanks in advance
Seagull6

Hi Seagull

As expected the ISERR function sorts it out

I've reattached your file with the fix in it, but I will also try and explain the 2 options here

Method 1

Example in cell S28

=IF(ISERR(R25),0,R25)+IF(ISERR(R26),0,R26)

As you can see all I have done here is built a sum, by saying if cell r25 has an error code in it, substitute the error with 0, if it is a number use this number
This will work but is very clunky and some of the formulas could get very large, as I see some of your sums are for up to 10 cells
This obviously is not the best solution, but it does work

You could nest the IF statements, however there is a limit of 5 nested IF statements so that will only work for some of them

method 2

Example in Column R

=IF(ISERR(GETPIVOTDATA("Time",psp2!$A$3,"Task Code",P5)),0,GETPIVOTDATA("Time",psp2!$A$3,"Task Code",P5))

With this one, I have taken your original formula and refined it, again all it says is that if the result of the getpivotdata formula is an error, then return a zero not an error, alternatively return the actual number

The other thing I did was to lose the hard coding of the task ID, and replace it with a cell reference, my theory here is that if you get a new task ID, all you should have to do is insert it into the sheet, and copy and paste the formulas and it should work

have a look, see what you think and let me know, any questions please feel free to ask

Way

Hi again Waylander
After spending yesterday struggling with your ISERR and ISBLANK suggestions, I am totally in awe of your ability to come up with a relatively simple solution. Many, many thanks.

Method 2 seems to be the way to go for me and the one I am most able to comprehend, and so will shoot with it. You've certainly given my sleep-deprived brain a boost this morning!!! Thanks heaps
Seagull6