Pivot table with Table as source data

[stewmander]

Hello, I am new to this board and am trying to create a simple excel data base using the "Table" function. I then want to use a pivot table to sum my database values. I followed excel magic trick (http://www.youtube.com/watch?v=WtcUYXVic-4) and got everything working properly (even figured out that pivot tables need to be refreshed), however, when I filter my table data base to show specific data entries, the pivot table data disappears. Why is this happening and is there a way to stop this, or do i need to use a named range function instead of the table tool? Thanks for the help (I know, Im new to this)

Stew

 

[Sthrncali]

You shouldn't be filtering on your Dtable. If you open the Pivot Table Field List, you can add fields that you want to filter by to the filter pane.

Then you can filter your data as needed directly from the pivot table..

 

[stewmander]

Thanks for the reply, I do understand that I should be filtering via pivot table, however i wasnt sure that if someone else were to use the spread sheet to look up the data then filtered using the database table, then wouldnt understand that they had to clear the filters in order for the pivot table to work. However, I did manage to solve the problem, when I redid my pivot table in a new sheet, the problem stopped.

I now have a different question - when I add data to my database, it automatically adds to my pivot table, and when i delete data entries from my database table, it removes it from the pivot table. However, when I go to the filter dialog boxes within the pivot table, the old, deleted data points are still visible as a check box. This does nothing to the pivot table, if they are checked nothing is displayed because there is no data to display. I am just worried that if i have a lot of data entered, then some removed, the removed data will still show up in the pivot table filter menu, and that check box list will continue to grow and grow and have a lot of unnecessary check boxes for data that no longer exists...any way to delete these old check boxes, or have my pivot table filter menu only display filter boxes for available data? Thanks for all the help

 

[Sthrncali]

If you uncheck those values and refresh your pivot table, they should disappear.

 

[stewmander]

Thanks for all the help! Unfortunately, when I unchecked the values that no longer have data associated with them, then refresh my pivot table, they still appear in my filter menu =/