 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Charting / September 2007Filter lines with Pivot table and non pivot table columns
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 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 > 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 [quoted text clipped - 5 lines] > 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 Thanks, this works for formulas, any suggestions if a number is entered into a column? > Hi, > [quoted text clipped - 25 lines] > > 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 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.  SignatureCheers, Shane Devenshire > Thanks, this works for formulas, any suggestions if a number is entered into > a column? [quoted text clipped - 28 lines] > > > 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 Thanks, this works for formulas - any suggestions if there is just a number entered? > 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 [quoted text clipped - 5 lines] > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.