GETPIVOTDATA looking for "" when pivot table uses "(blank)"

[j2yuj2yu]

Hi everyone,

I have a pivot table where some of the items in the row fields are blank. Here's an example:

Excel 2010

	A	B	C	D
12	B	Goleta	CA	120
13	D			#REF!
14	D	(blank)	(blank)	300

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D12 =GETPIVOTDATA("Total",$A$3,"Customer",A12,"City",B12,"State",C12) D13 =GETPIVOTDATA("Total",$A$3,"Customer",A13,"City",B13,"State",C13) D14 =GETPIVOTDATA("Total",$A$3,"Customer",A14,"City",B14,"State",C14) <tbody> </tbody>

<tbody>
</tbody>

Questions:

1. Why does Excel put "(blank)" in the pivot table? Is there an option to set it to "" instead?
2. Because the data for Customer, City and State comes from a database, I'd rather not be massaging the data replacing "" with "(blank)". Any suggestions?
3. Is there a better way to a multi-criteria lookup? I chose GETPIVOTDATA because it seemed to be very quick and not require a lot of CPU power. Is there another formula I could look at?

BTW, I noticed that GETPIVOTDATA("Total",$A$3,"Customer",A14,"City",,"State",) works, too. That doesn't work well with formulas, though.

Thanks in advance.

Jimmy

 

[RoryA]

Welcome to the forum.

You could amend your formula like this:

=GETPIVOTDATA("Total",$A$3,"Customer",IF(A12="","(blank)",A12),"City",IF(B12="","(blank)",B12),"State",IF(C12="","(blank)",C12))

or this:

=GETPIVOTDATA("Total",$A$3,"Customer",IF(A12="",,A12),"City",IF(B12="",,B12),"State",IF(C12="",,C12))

 

[j2yuj2yu]

Clever. Thanks!