Summarsing Variable Pivot Table Data

[Banjo Dave]

I have a pivot table giving the average length of stay of patients in
hospital for the previous month. This pivot table can be manipulated to show
different specialties and different locations.
I want to summarise this data on a seperate sheet, picking up the fields and
values when the different options are selected for location. So I would be
able to pick up average length of stay for say General Surgery patients at
two differnt hospital from the pivot table automatically (though these are
not displayed simultaneously on the pivot). Can I do this using VBA or a
macro and if so how do I go about it?

The values for say General Surgery at one location are not always in the
same cell position in the pivot as well (if I have a new months data imported
data or a new location selected on the pivot)

Example of Pivot Table below:

Month July 2006
Location Hospital1
Patient Type Inpatient
Method of Admission Elective

Main Speciality Speciality Consultant Average LOS No. of Episodes
General Surgery General Surgery Thomas, J 123.00 1
General Surgery Total 123.00 1
General Surgery Total 123.00 1
Orthopaedics Orthopaedics Phillips, R 45.00 2
Orthopaedics Total 45.00 2
Orthopaedics Total 45.00 2
OVERALL 71.00 9


Thanks very much,

 

[Debra Dalgleish]

Perhaps you could have a pivot table on a hidden sheet that displays all
the data, with fields in the row and column areas, instead of the page area.

Then, on the comparison sheet, use GetPivotData formulas to extract the
data from that pivot table, using references to cells that contain the
criteria.

There are instructions for GetPivotData formulas in Excel's Help, and here:

http://www.contextures.com/xlPivot06.html