get pivot data

  • Thread starter Thread starter RichardO
  • Start date Start date
R

RichardO

Hello All:

I have a get pivot data problem. I have 5 "States" that could occur.
The "States" field in the Row field.

I have the following formula:
GETPIVOTDATA("Count of Current Due",PTs!A4,"Transaction Workflo
State","SCI","due","Yes")+
GETPIVOTDATA("Count of Current Due",PTs!A42,"Transaction Workflo
State","A/P","due","Yes")+
GETPIVOTDATA("Count of Current Due",PTs!A74,"Transaction Workflo
State","Buff","due","Yes")+
GETPIVOTDATA("Count of Current Due",PTs!A92,"Transaction Workflo
State","Manager","due","Yes")+
GETPIVOTDATA("Count of Current Due",PTs!A124,"Transaction Workflo
State","Analyst","due","Yes")

Sometimes though only 1 of these states occur, so maybe only SC
occurs, or 2 states occur e.g. SCI and Manager states or all 5 state
occur. With the formula above, I get a REF# error if all the 5 state
don't occur.

Can you please tell me how I can go about capturing all the state
without getting a REF# error?

Thank you very much.


RichardO
 
You could wrap each GETPIVOTDATA formula with an IF formula, e.g:

IF(ISERROR(GETPIVOTDATA("Count of Current Due",PTs!A4,"Transaction
Workflow State","SCI","due","Yes")),0,GETPIVOTDATA("Count of Current
Due",PTs!A4,"Transaction Workflow State","SCI","due","Yes"))
 
Back
Top