Bring Only Filtered Data to Pivot Table

[marylynn]

How do you retain the data that is filtered in the main worksheet (we filter on 5 or more columns) when creating a Pivot Table?

Are we having trouble because we are using a Shared Workbook?

Doesn't the administrator (first person in) have rights to make all changes?

If Shared Workbook restricts this, how can we get around this limitation?

 

[Matty]

Hello marylynn and Welcome to the Board!

I'm not sure I entirely understand what you're trying to do, but here goes...

Let's say your dataset cover Cells A1:E100. If your Pivot Table was built using these Cells as its source data, it doesn't matter whether you then filter on this source data - your Pivot Table will still pull in all of the information held there.

Is there a reason you're not you're filtering on your source data and not within the Pivot Table itself?

Matty

 

[marylynn]

Thanks for the welcome!

Our main spreadsheet contains 52+ columns where we filter out data from columns that we do not need in the pivot table. We want to keep the pivot table a simple summary, uncluttered if possible.

I saw on another thread that Pivot Tables may not refresh with Shared Workbooks. Is bringing filtered data over to pivot tables another limitation of Shared Workbooks?

Thank you.

 

[Matty]

Hello marylynn,

I can't say I have ever used a Pivot Table within a shared Workbook, but I suspect problems will arise if you attempt to refresh the data when another individual is in that Workbook, potentially updating data that the PT is (trying) to use.

Another trick I have recently learned is to use MS Query (which is built into Excel) to filter a large dataset so that your PT only displays the data you want to see.

Have a look here:

http://www.exceluser.com/explore/msquery1_1.htm

Hopt this helps.

Matty

 

[marylynn]

Thanks, Matty. I will look into this next week.

Bye for now...