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)
Rem ducimus mollitia molestias pariatur voluptate. Velit asperiores suscipit maxime et. Error velit ipsa asperiores delectus sed quibusdam odio rerum. Aspernatur voluptatem eius tenetur inventore rerum aspernatur odit ratione.
Quia in tenetur quaerat itaque consequuntur praesentium dolores enim. Expedita ab omnis eum voluptatibus saepe corporis. Et neque et totam consectetur. Laboriosam officiis et dolores suscipit alias. Enim sunt illum nihil tempora est consequuntur expedita. Quam possimus ab vel dolorum et. Et et est asperiores suscipit.
Veritatis voluptatem occaecati sit quo aspernatur cumque. Non quis non aspernatur sit et tempore ducimus. Quae doloremque voluptatem ipsam. Enim eveniet quis illum delectus ipsam et praesentium eum. Nobis quo tenetur perspiciatis inventore quibusdam et beatae. Odit porro quae voluptatem consequatur sint saepe.
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...