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.
