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
B	Goleta	CA
D
D	(blank)	(blank)

<tbody>
[TD="align: center"]12[/TD]

[TD="align: right"]120[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]#REF![/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]300[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D12[/TH]
[TD="align: left"]=GETPIVOTDATA("Total",$A$3,"Customer",A12,"City",B12,"State",C12)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D13[/TH]
[TD="align: left"]=GETPIVOTDATA("Total",$A$3,"Customer",A13,"City",B13,"State",C13)[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D14[/TH]
[TD="align: left"]=GETPIVOTDATA("Total",$A$3,"Customer",A14,"City",B14,"State",C14)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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!