Linking to Pivot Table Data and Dragging Reference Down/Across

I am creating a second table (non pivot) based on the data in a pivot table. I cannot figure out how to reference the pivot table cells and be able to drag the reference to adjacent cells while updating accordingly.

I can get the first cell reference to display correctly, enter "=", then click on the pivot table cell I want to display. However, if I try to drag this reference down to the whole row, it continually displays the data from the first cell only.

I have tried adjusting the "$" in the formula but this doesn't seem to make any difference in what data is displayed. The formula will appear to be correct in the cell, but will only display the data from the first cell.

=GETPIVOTDATA("Salary Exp",'State Federal Pivot'!A4,"Country","A")

=GETPIVOTDATA("Salary Exp",'State Federal Pivot'!A5,"Country","A")

These will both display the exact same data, whichever cell I explicitly clicked on first.

I don't want to assign the cells by clicking on each one, is there a trick to dragging a pivot cell reference?

This thread is locked. You can vote as helpful, but you cannot reply or subscribe to this thread.

Replies (1)

Sheeloo

See https://support.microsoft.com/en-in/help/287736/getpivotdata-formula-is-automatically-created-when-you-try-to-create-s

Workaround:

Instead of typing = and then clicking on the PivotTable cell type the cell address

eg. if you want to refer to cell B2 in the PivotTable just tyoe

=B2 instead of typing = and then clicking on the cell

To get rid of GETPIVOTDATA for all PivotTables

Excel 2016

Open Options and click on Formulas on the left pane

then clear the Generate GetPivotData check box. It is in the second group labelled 'Working with Formulas

If this response answers your question then please mark as Answer. It helps others who browse.

Linking to Pivot Table Data and Dragging Reference Down/Across

Replies (1) 

Question Info

Replies (1)