Archive of Mr Excel Message Board


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



Pivot Table & Lookup

Posted by SJC on January 30, 2001 5:59 PM
I have workbook (call it workbook A) containing a pivot table that shows data with status (red, yellow, green) on the column and category (car, plane, ship) on the row like this:
__________Car____Plane____Ship
Red_________1________3_______7
Yellow______9________8______12
Green______15_______17_______3
From month to month, the data varies such that the size of the pivot table is not always 3x3 (i.e. there might be less than 3 status and/or less than 3 categories).

My problem is that I have another workbook (call it workbook B) that I want to reference data in the pivot table in workbook A. Workbook B always has all 3 statuses and all 3 categories. Is there a way to combine VLOOKUP and HLOOKUP formulae to accomplish this task? For a particular month, if there are only 2 statuses and 3 categories, I would like to be able to show zeros in the missing status for workbook B.

Am I making any sense?


Re: Pivot Table & Lookup

Posted by Chance on January 30, 2001 6:32 PM

Somewhat .... I have used the function GETPIVOTDATA to successfully lookup values from a pivot table but only within the same worksheet ... I don't have the exact syntax (it's at on of my clients) but you can find it in the Help books. When I go out to the clients, I will copy it out and send another follow-up here....but it'll be a few days. Let me know if you are able to resolve it from the help books.


Chance, This Is Awesome! Many Thanks!

Posted by SJC on January 31, 2001 9:21 AM




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.