Archive of Mr Excel Message Board

Back to Pivot Tables in Excel archive index
Back to archive home

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

Re: GETPIVOTDATA problem with two matching fields

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.

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.