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:
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?