GETPIVOTDATA Help

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.

 
Best Response

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)

 

Architecto nisi commodi qui placeat. Optio veritatis nesciunt ut enim aspernatur dolores. Ut optio dolor qui sed quae similique.

"Well, you know, I was a human being before I became a businessman." -- George Soros

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (85) $262
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (65) $168
  • 1st Year Analyst (198) $159
  • Intern/Summer Analyst (143) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”