Hello,

I have cells in one worksheet which contain formulas referring to cells in a PivotTable in another worksheet. I've used the GETPIVOTDATA function to set the formulas, and they look like this:

=GETPIVOTDATA("Sum",OtherSheet!$A$3,"Class","RCO","Date'",DATE(2007,7,1))

However, the PivotTable may be moved to another location within the "OtherSheet" worksheet, and so I'd like for all my formulas to continue to work if the table is moved. Ideally I'd like to replace the "$A$3" reference in the formula above to one utilizing the name of the PivotTable. I've tried variations of formulas like:

=GETPIVOTDATA("Sum'",OtherSheet!PivotTables("NameOfPT").tablerange2,"Class'","RCO","Date'",DATE(2007,7,1))

I can't find anything to work without getting some kind of #NAME? error or similar.

Does anyone know how to refer to a PivotTable location by name with the GETPIVOTDATA function?

An alternative idea is to use a cell reference without the GETPIVOTDATA function. Since I know exactly which absolute column to reference on the other sheet, if I could use some function or property of the PivotTable that returns the absolute row number for a specific item (e.g. "RCO" in the above example) in the rowrange, then I could use that row and the column I already know as the reference in the cell formula. If anyone knows how to do that, I would appreciate that as well!

Many thanks!