ExcelBanter - Get Pivot Data

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Get Pivot Data (https://www.excelbanter.com/excel-discussion-misc-queries/101002-get-pivot-data.html)

When the value in a pivot table field is blank (as below) what is the
expression in the GETPIVOTDATA formula that returns $0.10 instead of $0.40?

Main Unit Revalue R vs NR Jul-06
Sales (blank) R $0.10
NR $0.30
Sales Total $0.40

=GETPIVOTDATA(PIVOTS!A3,"sales€) returns $0.40

Since I want to extract the value $0.10 I would normally write

=GETPIVOTDATA(PIVOTS!A3,€sales (blank) €˜7/1/2006€)

But in this instance that returns #REF! or #NA

I don't know of a way to return a value for (blank) in Excel 2000 (it
works in Excel 2003).

You could select the (blank) heading and type another heading there,
even just a space character. Then use that value in the GetPivotData
formula.

Benjamin B wrote:
When the value in a pivot table field is blank (as below) what is the
expression in the GETPIVOTDATA formula that returns $0.10 instead of $0.40?

Main Unit Revalue R vs NR Jul-06
Sales (blank) R $0.10
NR $0.30
Sales Total $0.40

=GETPIVOTDATA(PIVOTS!A3,"sales€) returns $0.40

Since I want to extract the value $0.10 I would normally write

=GETPIVOTDATA(PIVOTS!A3,€sales (blank) €˜7/1/2006€)

But in this instance that returns #REF! or #NA

--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html