I have a massive pivot table full of data with respect to NGL trades my firm has completed and am linking the data cells from the pivot into another workbook. I found a way to automate the dumping of row data but for some reason I'm having a hard time automating the columns. The formula currently appears as follows:
=GETPIVOTDATA("Sum of TOTAL NGL VALUE",'[Consolidated processor CSV.xlsx]Pivot'!$A$3,"processor PLANT NAME",$A$1,"ACCOUNTING DATE","032015","Meter Number 2",$A4)
The problem lies in the "Sum of TOTAL NGL VALUE" which is the column that the formula is referencing. Is there a way around this issue? Currently I'm manually linking up every single column and then the row pasting is automated but it's a massive pain in the ass.
Copy the TEXT of which column you want to copy, then paste that text within the " " where the column reference currently is. Hard to try and explain since I am not 100% sure that addresses your issue, but it sounds like then each row will paste based on other criteria in the formula, but then you are adequately referencing the column you want.
Now thinking about it, is your issue where you drag it across column's and the "Sum of TOTAL NGL VALUE" stays the same, where you want it to reference a column over? Will have to rethink if that is.
I think now I got it figured out...
So You have your pivot table column's with their various names, but you want the text of what is in it so what you will do is modify the formula like i explain below:
=GETPIVOTDATA(INPUT FORMULA LISTED BELOW HERE,'[Consolidated processor CSV.xlsx]Pivot'!$A$3,"processor PLANT NAME",$A$1,"ACCOUNTING DATE","032015","Meter Number 2",$A4)
in the first section use the formula T below:
T(Click on cell reference for whatever column in the pivot table you want)
When you use a relative reference and drag it to the right or left it will then reference the column's now. You probably need to lock the row's but not column's for your relative reference so it keeps that reference when you drag downward.
Here is my example I was doing in my own spreadsheet to test:
=GETPIVOTDATA(T('Management Dashboard'!B5),'Management Dashboard'!$A$5,"Date",$B$2,"Years",$A$2)
Quaerat quis quia nobis sint. In dignissimos omnis nostrum facilis soluta et. Saepe optio possimus sed velit eos. Deleniti iste quo quod voluptatem deserunt distinctio ullam.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...