GetPivotData

[abbymagga]

I have a GetPivotData Function =GETPIVOTDATA("VPMO_PROJ_ID",'TCO Pivot (2)'!$A$9,"Type","TCO Active","Supervisor","TBD") and it brings back the correct value, but when I drill down on the value, it brings me to the top left corner of the pivot table and not to the cell that contains the value. Can this be done?
I can do a cell reference, but then I lose the dynamic capability of the GetpivotData function.

Any help would be greatly appreciated. I am using Excel 2007

[romperstomper]

Could you clarify this: "but when I drill down on the value"? If you mean it selects A9, that's because A9 is the only cell referenced by your formula. There is no way to alter that behaviour.

[abbymagga]

that assumption is correct. I was wanting it to select the value associated with Type = TCO and supervisor = TBD instead of the Pivot table cell.

I have attached a bmp copy of my pivot table and where I was hoping the GetPivotdata function would take me.

I have tried to upload my file as a zip, but I get a DB error on this site..

[romperstomper]

You can't do that. You are not drilling down on the pivot table, you are simply using Excel's native function that jumps to cells you have referenced directly in the formula.

[abbymagga]

Do you know of anyway I can do it dynamically? I know I can use a cell reference, but if more supervisors are added then it would refer to the wrong cell.

[romperstomper]

You would have to write some code to intercept the double-click event of the sheet, locate the row/column of interest and then select it. There is nothing built-in that will do what you want unless you use a direct cell reference, which is a bad idea.

[abbymagga]

ok. Thanks!!