PDA

View Full Version : Pivot Table and Formula



chicity
06-25-2013, 02:52 PM
I have created a pivot table from the data I had extracted from sql database. I added my own formula in the column that's next to the pivot table that takes a weighted average. The problem I'am having is when I change the dates on the data table it refreshes and the pivot table either expands or decreases and the formula I created doesn't move with the pivot table. Please help what should I do :banghead: :banghead: :banghead:

JWhite
06-26-2013, 09:10 AM
Instead of referring to a particular cell on the spreadsheet you have to specify a position in the pivottable. Fortunately there's a tool for this. Here's the quick version of how to do what you want.

Go to a cell outside your pivottable and type "=" and then arrow over to a cell in the pivottable you're using and hit enter. You'll see that Excel doesn't reference the cell as "C48" or whatever. It creates a long formula called a "GETPIVOTDATA" and the values in the formula tell it where to go in the pivottable to get that value. If you then refresh the pivottable and it changes shape that formula will still return the correct value even if the cell has moved.

So instead of referencing specific cells you want to use "Getpivotdata" formulas to find your values.

chicity
06-26-2013, 10:55 AM
I tried doing that, but as the dates change and the pivot table refreshes it doesn't keep the formula because the name of the accounts don't match up with the ones that where there before. Is there a vba code ?

JWhite
06-27-2013, 03:18 PM
I've always been able to make it work. You have to know all the relevant parameters, of course. Maybe if you post the spreadsheet with more information someone could help you. I'm going to be very busy so I won't be able to look into it. Good luck.