All solutions General QuestionsPIVOT TABLE Expert Solution – General Questions on Pivot Table

Excel - General Question on Pivot Table - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I have a pivot table with data points per month and I am putting that into a spreadsheet (a budget). My problem is that I can't figure out to escalate each data point per column, i.e. ='Pivot Table 1'!E3 then the next column pulls from ='Pivot Table 1'!E4, the next ='Pivot Table 1'!E5...SO, how do I write a formula the right way?
Solved by E. C. in 11 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 19/09/2018 - 01:38
Hi! How are you? I can see that you're having problem with your Pivot Table data referencing.
User 19/09/2018 - 01:38
Yes.
User 19/09/2018 - 01:38
I have a pivot table with data in it
User 19/09/2018 - 01:39
and I am pulling from it to another sheet (a P&L)
Excelchat Expert 19/09/2018 - 01:39
Cool. I can help you with that.
Excelchat Expert 19/09/2018 - 01:39
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User 19/09/2018 - 01:39
OK
Excelchat Expert 19/09/2018 - 01:39
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User 19/09/2018 - 01:39
ok
Excelchat Expert 19/09/2018 - 01:40
Firstly, is it possible to ask for a copy of your worksheet? It is easier in my end if I get to visualize your data structure clearly.
User 19/09/2018 - 01:40
i can add a dummy version here, yes
Excelchat Expert 19/09/2018 - 01:40
Thank you!
User 19/09/2018 - 01:40
actually, it's locked
User 19/09/2018 - 01:40
so i can't
User 19/09/2018 - 01:41
but i can recreate a very simple version her
Excelchat Expert 19/09/2018 - 01:41
That would be very helpful
User 19/09/2018 - 01:42
How do I get Feb to auto populate, i.e. +1 from the Jan values
Excelchat Expert 19/09/2018 - 01:43
You mean Feb will pick up the value of Pivot table cell B3?
User 19/09/2018 - 01:43
Yes
User 19/09/2018 - 01:43
and so on and so forth
Excelchat Expert 19/09/2018 - 01:44
We can use the TRANSPOSE function for this, given that the columns are of the same ascending order with the rows in your pivot table.
User 19/09/2018 - 01:44
Can you show me how here?
User 19/09/2018 - 01:44
so the formula doesnt need to be in every cell?
Excelchat Expert 19/09/2018 - 01:44
Sure. The formula is in Sheet 1 now beginning cell B3
User 19/09/2018 - 01:45
So the formula only needs to be in the first cell of the row?
Excelchat Expert 19/09/2018 - 01:45
If you're using Google sheets, yes.
Excelchat Expert 19/09/2018 - 01:45
A little bit different with Excel.
User 19/09/2018 - 01:46
And I assume the same thing goes for transposing Column data
Excelchat Expert 19/09/2018 - 01:46
In Excel, you have to highlight cells of the rows where you want your data will be transposed to. Then type on the first cell (i.e., B3) =TRANSPOSE(reference range in Pivot Table) and press CTRL+SHIFT+ENTER to activate Array Formula.
User 19/09/2018 - 01:47
i only use sheets now!
Excelchat Expert 19/09/2018 - 01:47
You will know it's an array formula when the formula is enclosed in brackets :)
Excelchat Expert 19/09/2018 - 01:47
Then the formula is as is with what you see in the document preview :)
User 19/09/2018 - 01:48
So to confirm, for Sheets, I only need the formula in one cell
User 19/09/2018 - 01:48
and it will transpose across the row
Excelchat Expert 19/09/2018 - 01:48
Correct. :)
User 19/09/2018 - 01:48
wow. i am super impressed with your service
Excelchat Expert 19/09/2018 - 01:48
You can try the same formula in cell B4.
User 19/09/2018 - 01:48
I will ABSOLUTELY be using you for future issues
Excelchat Expert 19/09/2018 - 01:48
Happy to hear that :)
Excelchat Expert 19/09/2018 - 01:49
Does this solution solve your problem?
User 19/09/2018 - 01:49
Thank you again!
User 19/09/2018 - 01:49
yes!
Excelchat Expert 19/09/2018 - 01:49
Cool!
Excelchat Expert 19/09/2018 - 01:49
If you don't have any more clarifications, you may end the session any time now by clicking the END SESSION button on the upper right corner of the workspace (that's beside the countdown timer). :)

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc