working with pivot tables from another worksheet

[Leora Radetsky]

Hi all
This is probably a very rudimentary post but I can’t figure out how to
work with a pivot table. I am using Excel 2007. I have 5 tabs in my
worksheet with pivot tables. The front end of my worksheet is an
interface for end users. One of their inputs is a time range. What I
would like to have happen is to use their time range (say 10AM to 2PM)
and filter my pivot table using that range so I can get the average of
another columns data once filtered. I don’t want them to use the
filter themselves (I just want to show them the changing average as
they change their time) and do some calculations with this dynamic
average.
Do I need a macro to do this or is there a built in excel function I
haven’t been able to find? Please help. Pivot tables seem like the
perfect answer but I need to access the filter options from another
worksheet.
Thank you for your expertise.

Leora

 

[ShaneDevenshire]

Hi,

Well if you don't want them to do the filtering themselves you will need
code I believe.

 

[Roger Govier]

Hi Leora

take a look at Debra Dalgleish's site.
Debra has lots of example files and one which you might modify to sit your
purpose is
http://www.contextures.com/PivotDateRange.zip

Here the filter is being set on a date range, but could be easily modified
for Time.
Debra also has examples of changing multiple page fields from a single set
of criteria.
http://www.contextures.com/PivotMultiPagesDiffChange.zip
Between the two you should manage to do what you want. If not, then post
back with what you have and maybe we can help you further.