Hey all,
I'm working with several pivot tables and using GETPIVOTDATA to pull my information into my exhibit.
Currently my formula looks like this: (and works just fine)
My updated formula using an array for GP1:
If 001, 004, and B34 are all in the data for the month, no problems. If any of them are absent, they return a #REF error and a 0 for the final count. The AGT can change month to month so I can't easily have a static list in the array. Which brings another question... am I able to use a dynamic named range in the GETPIVOTDATA function? That's something else I tried that didn't work.
Thanks!
I'm working with several pivot tables and using GETPIVOTDATA to pull my information into my exhibit.
Currently my formula looks like this: (and works just fine)
Code:
=IFERROR(IF(eAGT="All",GETPIVOTDATA("PolNumber",Q!$B$3,"State",xState),GETPIVOTDATA("PolNumber",Q!$B$3,"State",xState,"GenAgt",eAGT)),0)
My updated formula using an array for GP1:
Code:
{=IFERROR(IF(eAGT="All",GETPIVOTDATA("PolNumber",Q!$B$3,"State",xState),IF(eAGT="GP1",SUM(GETPIVOTDATA("PolNumber",Q!$B$3,"State",xState,"GenAgt",{"001";"004";"B34"})),GETPIVOTDATA("PolNumber",Q!$B$3,"State",xState,"GenAgt",eAGT))),0)}
If 001, 004, and B34 are all in the data for the month, no problems. If any of them are absent, they return a #REF error and a 0 for the final count. The AGT can change month to month so I can't easily have a static list in the array. Which brings another question... am I able to use a dynamic named range in the GETPIVOTDATA function? That's something else I tried that didn't work.
Thanks!