Getpivotdata formula cell referencing

[PowerZ]

Hi,

How come doing this doesnt work?

=GETPIVOTDATA("Payment",PivotTables!$B$25,"Year",Formulas!J4)

I want to have the item at the end link to a cell reference so it can change.

Any idea on how to make that work?

Thanks.

[arlu1201]

Attach a sample file so we can make it work for you. I have used similar functions often and they work fine.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[PowerZ]

Theres a lot of data and it'll take quite a while to remove the sensitive parts.

The formula works fine when the reference at the end is replaced with the text e.g. "2014", but it gets a #REF error when theres a cell reference

[Olly]

Should work, as long as Formulas!J4 contains a value which matches the values in your Year field. #REF error suggests the two data types / values don't match.

How is your Year field formatted (numberformat) and does that match Formulas!J4 ?

Try attaching a workbook so we can see what the problem is.

[PowerZ]

Should work, as long as Formulas!J4 contains a value which matches the values in your Year field. #REF error suggests the two data types / values don't match.

How is your Year field formatted (numberformat) and does that match Formulas!J4 ?

Try attaching a workbook so we can see what the problem is.

I have attached a sample workbook outlining the error.

Hopefully you can see where I am going wrong.

[Olly]

As suspected, it's a numberformat issue. The data feeding your pivot table has years formatted as text, the cell you reference containing the desired year is formatted as a number.

Easy fix - change your getpivot formula to:

Please Login or Register  to view this content.

OR change the year cell to:

Please Login or Register  to view this content.

OR change your source data to store years as numbers, not text - e.g. in cell C4 of your example:

Please Login or Register  to view this content.

[PowerZ]

As suspected, it's a numberformat issue. The data feeding your pivot table has years formatted as text, the cell you reference containing the desired year is formatted as a number.

Easy fix - change your getpivot formula to:

Please Login or Register  to view this content.

OR change the year cell to:

Please Login or Register  to view this content.

OR change your source data to store years as numbers, not text - e.g. in cell C4 of your example:

Please Login or Register  to view this content.

Ah wicked!

Thanks for the fast reply and the clarification on this.

[Izandol]

You may also use simply:

=GETPIVOTDATA("Payment",$A$16,"Year",""&year!B3)

[vlady]

FORUM MODERATOR'S REQUEST:

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.