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)
Laborum similique harum explicabo ut. Iure officiis aperiam in iure ipsam. Iste cum quis numquam ut. Adipisci molestias et vel quia eveniet. Harum voluptates officiis eos quos aut labore ullam.
Quis quia nesciunt provident tenetur eum quasi necessitatibus. Velit fugit repellat animi. Dolorum dignissimos sed rerum non sint. Qui similique incidunt quo sit asperiores. Tenetur est eius aliquam eius cum amet impedit excepturi.
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...