Pivot Table Filter or Something Else? (2002)

Topic

New Reply

1) I am trying to create a pivot table with data that is within a rolling 12 months. It will be used once a month with new data for the new month so data will have to be refreshed. In the column field is a date. I

Reply | Quote

Replies

You want
today() not date()
[but beware of leap years]

You might check out how Joh Peltier defines dynamic ranges to plot the last 12 months. This technique of naming the range could be used to (instead of plotting) to define the range of the pivot table. If the range of the pivot is a dynamic range, when ever you refresh, it will do it based on the range which changes as the data changes.

Steve

Reply | Quote

Steve, thank you for your reply. I looked at Jon

Reply | Quote

I don’t think that you can create a pivot with filtered data as the source. The source would be the entire range not just the visible cells.

You can copy the filtered dataset to a new sheet and create the pivot from this. You could also use a pivot based on the whole dataset and then use page fields as a filteriing device. The page fields work similar to the filters and allow interaction with the table.

Steve

Reply | Quote

Hi Steve. Yes I think you are right. I don’t think I can create a pivot table base on filtered source. I did manage to use advanced filters and copy the data (only the last 12 months worth) to another spreadsheet. Then I took that data and created a pivot table. Turned out really well. Thanks again.

Reply | Quote

Hi Steve. Yes I think you are right. I don’t think I can create a pivot table base on filtered source. I did manage to use advanced filters and copy the data (only the last 12 months worth) to another spreadsheet. Then I took that data and created a pivot table. Turned out really well. Thanks again.

Reply | Quote

I don’t think that you can create a pivot with filtered data as the source. The source would be the entire range not just the visible cells.

You can copy the filtered dataset to a new sheet and create the pivot from this. You could also use a pivot based on the whole dataset and then use page fields as a filteriing device. The page fields work similar to the filters and allow interaction with the table.

Steve

Reply | Quote

Steve, thank you for your reply. I looked at Jon

Reply | Quote

You want
today() not date()
[but beware of leap years]

You might check out how Joh Peltier defines dynamic ranges to plot the last 12 months. This technique of naming the range could be used to (instead of plotting) to define the range of the pivot table. If the range of the pivot is a dynamic range, when ever you refresh, it will do it based on the range which changes as the data changes.

Steve

Reply | Quote