Cell Reference in GETPIVOTDATA formula is getting deleted

[jwar87]

Hi, I have really been struggling with this and have no Idea what to do. Here is my formula:

=IF(ISERROR(GETPIVOTDATA("Flavour Trend",UNFIFlavourTrend!$B$3,"Date","Mar")),0,GETPIVOTDATA("Flavour Trend",UNFIFlavourTrend!$B$3,"Date","Mar"))

When I get new information I paste it in the sheet labeled UNFIFlavourTrend, I then run the macro, and out pops a pivot table. This is a formula that I am using to retrieve and summarize information on another sheet from ther pivot table. However, when I paste new data in and run the macro, the cell reference $B$3 turns to #Ref. Is there anyway I can lock this cell reference as $B$3 in the formula?

 

[General Ledger]

jwar87,

What if you name the cell $B$3 (something like "Home") and use the name in your formula:

=IF(ISERROR(GETPIVOTDATA("Flavour Trend",Home,"Date","Mar")),0,GETPIVOTDATA("Flavour Trend",Home,"Date","Mar"))

G/L

 

[jwar87]

jwar87,

What if you name the cell $B$3 (something like "Home") and use the name in your formula:

=IF(ISERROR(GETPIVOTDATA("Flavour Trend",Home,"Date","Mar")),0,GETPIVOTDATA("Flavour Trend",Home,"Date","Mar"))

G/L

Hi,

Thanks for the quick reply! I created the name as you suggested. Now when I input new data and run the macro the name I created stays in the in the Formula like you showed above; however, the reference in the Name changes ie. Referenced cell for name Home changes from SheetName!$B$3 to SheetName!#Ref.

My uneducated hypothesis: The macro is deleting the initial $B$3 cell. Maybe I should use another cell with in the pivot? With my formula $B$3 can be any cell with in the Pivot table.

Hope this make sense!

 

[General Ledger]

jwar87,

Interesting. You may be on to something in your hypothesis. If Excel is deleting the cell $B$3, then use the relative cell address. Try this:

=IF(ISERROR(GETPIVOTDATA("Flavour Trend",UNFIFlavourTrend!B3,"Date","Mar")),0,GETPIVOTDATA("Flavour Trend",UNFIFlavourTrend!B3,"Date","Mar"))

G/L