Getpivot date w/ relative reference


S

Spidey

I am or have been attempting to extract data from a pivot table. I would
like to use a reference in "sheet 1" cell $A1 as the "item" in the
getpivotdata (the pivot table is in a separate sheet) formula below.

=GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11")

=GETPIVOTDATA(data_field,pivot_table,field1,item)

all my efforts have resulted in #ref results.

Any insight would be greatly appreciated
 
Ad

Advertisements

D

Debra Dalgleish

While creating the formula, you should be able to go to Sheet 1 and
click on the cell that you want to reference. Using your example, where
the "11" is a cell reference on Sheet 1:

=GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",'Sheet 1'!J2)
 
S

Spidey

Thanks for your response, but I am still at a loss:

If i use this formula:
=GETPIVOTDATA("3Q06",$A$3,"Linking - Building code","11")
It will return the correct answer of 405.

However, if i simply change the "11" to the cell reference J2 (which
contains the number 11), i get the error #ref.

=+GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2)

Is there some syntax that I am missing????
 
D

Debra Dalgleish

The building codes in your pivot table are text, instead of real
numbers. The 11 that you typed in the cell is a number, and "11" in the
formula is text, so they'll return different results.

In the cell, if you type an apostrophe before the number: '11
or format the cell as Text, then enter a number, the reference should
work correctly.
 
S

Spidey

i appreciate your help; but i did it both ways: format as text and '11. Any
other suggestions because i am sure at a loss
 
D

Debra Dalgleish

What's the result if you try:

=GETPIVOTDATA("3Q06",$A$3,"Linking - Building code",J2&"")
 
Ad

Advertisements

S

Spidey

Wow, now i tried "=" & j2 and it didn't work. But Js&"" worked perfect!
Now, how did you know to do that? What does it represent? And thank you for
your help and time.
 
D

Debra Dalgleish

You're welcome! Joining an empty string ("") to a number is another way
to change a number to text. Since the other techniques didn't work for
you, I hoped this one would.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top