**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.*