GETPIVOTDATA problem with two matching fields

Posted by Mark Jeanes on September 27, 2001 11:14 PM

Hi,

Say you have two columns down the left hand side of a pivot table - a customer number and part number. Let's say that sometimes a customer number just happens to be the same as a part number, even though they are not related.

How do you use getpivotdata to pull off the amount that combination?

e.g:

custno partno amount
301 456 $567
454 $678
301 $432
302 456 $235
454 $67
301 $233

How do i get the amount for 301(custno) and 301(partno) (e.g. $432)?

Thanks.

Mark

Posted by Mark W. on September 28, 2001 5:41 AM

As you've already discovered the GETPIVOTDATA
function cannot distinquish between identical
items from different fields. Consider the
addition of a field-related prefix to help
differentiate between these items. For example,
if you add a "C" prefix to your customer numbers
and a "P" prefix to your part numbers then
=GETPIVOTDATA(ref,"C301 P301") will produce 432.