reference another cell in getpivotdata function
 

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?

reference another cell in getpivotdata function
 

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

--
Regards

Roger Govier


"Sarah (OGI)" wrote in message
...
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?

reference another cell in getpivotdata function
 

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" wrote:

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

--
Regards

Roger Govier


"Sarah (OGI)" wrote in message
...
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?

reference another cell in getpivotdata function
 

Hi Sarah

The normal format of the GetPivotData would be
=GETPIVOTDATA("Units",$A$3,"Item","Binders","Colou r","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

--
Regards

Roger Govier


"Sarah (OGI)" wrote in message
...
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" wrote:

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

--
Regards

Roger Govier


"Sarah (OGI)" wrote in message
...
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?

reference another cell in getpivotdata function
 

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!

"Roger Govier" wrote:

Hi Sarah

The normal format of the GetPivotData would be
=GETPIVOTDATA("Units",$A$3,"Item","Binders","Colou r","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

--
Regards

Roger Govier


"Sarah (OGI)" wrote in message
...
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" wrote:

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

--
Regards

Roger Govier


"Sarah (OGI)" wrote in message
...
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?