Excel - Pivot Table - Show All

[anthonyT]

Hi All

Need to link data in a pivot table to a formated page. As such it would be handy to get the pivot table to remain the same size and have the data appear in the same order regardless of whether there is actualy any reslust...

EG
Current pivot table

Rate Count
AAA 2
AA 4
A 3
B 1


Required result:

Rate Count
AAA 2
AA+ 0
AA 4
AA- 0
A+ 0
A 3
A- 0
BBB+ 0
B 1

 

[Fazza]

Hi, Anthony.

If you must stay with a pivot table, maybe the easiest way is to use GETPIVOTDATA formula in the destination cells. And return 0 if there is an error with the function. Along the lines =if(iserror(getpivotdata(),0,getpivotdata())

HTH, Fazza

 

[anthonyT]

cheers Faza

Worked it - went with an if(isna(vlookup...with a 0 as the errror.

Thanks

 

[Fazza]

Good work, Anthony. Fazza