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?
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.
Posted by SJC on January 31, 2001 9:21 AM
Chance, This Is Awesome! Many Thanks!
