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?

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.

32 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated August 11, 2020 Views 7,370 Applies to: