reference another cell in getpivotdata function

[Sarah (OGI)]

I've asked a similar question already but didn't get a workable resolution so
thought I'd ask again.

I've entered the following formula into one worksheet, referencing a pivot
table in another worksheet (using the getpivotdata function):

=GETPIVOTDATA('USE THIS! - PIVOT TABLE'!A5,"1240 A TOTAL")

This works fine, pulling out the correct figure from the table, but rather
than entering a specific client, via the '1240' (which is a client ref), I'd
like to cross reference a cell in column A (on the same row) in a sort of
vlookup function - the client refs may change as this is a worksheet that is
updated regularly.

Any ideas?

 

[Roger Govier]

Hi Sarah

If your 1240 is in say cell A4 of the PT report, then substitute 1240
with $A4&"".

GETPIVOTDATA needs a null string either before or after the cell
reference

 

[Sarah (OGI)]

The 1240 is cell A10 of another worksheet (where the getpivotdata formula is
being entered). I want to take the value from this cell (Calculator!$A10)
and find it in the first column of the pivot table (in 'USE THIS! - PIVOT
TABLE'!A5). Once the correct ref is identified, I then want to extract the
value according to "A TOTAL".

I tried what you suggested, but I'm getting an #N/A. I entered:
=GETPIVOTDATA('USE THIS! - PIVOT TABLE'!$A$5,$A10&"""A TOTAL")

 

[Roger Govier]

Hi Sarah

The normal format of the GetPivotData would be
=GETPIVOTDATA("Units",$A$3,"Item","Binders","Colour","Blue")

Where Binders would be one of the values of Items, which is a field
added to the Row area
and where Blue would be one of the values in Colour which is a field
added to the Column Area

This could be amended to
=GETPIVOTDATA("Units",$A$3,"Item",Calculator!$A10&"","Colour",Calculator!B$9&"")

I think you may have something wrong with basic format of the formula
you first posted.
For further help, take a look at Debra Dalgleish's site

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

 

[Sarah (OGI)]

Thank you so, so much for your help!! - this has been driving me mad, but
finally your advice and the link to debra's site has solved the problem!!

Instead, I entered:
=GETPIVOTDATA('USE THIS! - PIVOT TABLE'!$A$4,"A TOTAL " &A10)

There's always a way to get these things done in Excel but it's working out
exactly how to do it that causes the headaches.

Thank you again!