Filter lines with Pivot table and non pivot table columns

[Grover]

I have created a pivot table. Next to the pivot table are columns to enter
data and then additional clolumns to add the pivot table and not pivot table
data. I am running a filter on a column in the pivot table, and the non
pivot table data entered does not hold with the pivot table line. (For
example, I have an line in the pivot table (say line 20) call "item 1". I
enter a value in a Column outside the pivot table to correspond to item 1 on
line 20. When I filter on "item 1" - it now moves to line 5; however, the
amount I manually added to line 20 outside the pivot table stays on line 20,
but I want it to move to line 5 with the pivot table data

 

[ShaneDevenshire]

Hi,

You need to use the GETPIVOTDATA function to allow formulas that are outside
the pivot table to "track" the entries in the pivot table.

Excel will build this function automatically on the PC (not on the MAC) The
toolbar button for turning this feature on and off is found by choosing View,
Toolbars, Customize, selecting the Commands tab and picking the Data
category. Almost at the bottom of the list of buttons on the right you will
find the GETPIVOTDATA button. Drag it to one of your toolbars, I put in on
the PivotTable toolbar.

Test if by click it on or off and somewhere outside the pivot table type =
and then clicking somewhere inside the pivot table. You will see the
GETPIVOTDATA function if the tool is on otherwise you will get a formula like
=B7.

Cheers,
Shane Devenshire

 

[Grover]

Thanks, this works for formulas - any suggestions if there is just a number
entered?

 

[Grover]

Thanks, this works for formulas, any suggestions if a number is entered into
a column?

 

[ShaneDevenshire]

I'm not sure quite what you mean. Is the number in the column area of the
pivot table or in a cell of the spreadsheet. Once Excel build the
GETPIVOTDATA formula you can modify it by changing hard coded entries such as
"234567" to cell references where you can enter any number or text that
matches a pivot table item value.