Filtering sheets in workbook

[Guest]

I have a workbook with multiple sheets with the same column headers. Each
worksheet represents a year of data. Is there a way to filter each page of
the workbook on the same Autofilter without having to set each one? For
instance, if I autofilter on a name in one, could I have it filter for all
worksheets? Ideally, I'd like to be able to save the worksheet based on any
name when it is requested and that is time consuming. Also, once I've
filtered is there anyway to combine the data from each worksheet all into one
based on all years for that name?

Thanks,
Kristin

 

[icestationzbra]

there are two ways that i can think of, for the first part of you
question. both involve VBA.

you could record a macro for Advanced Filter, in which you set th
criteria on the first page that is subsequently used by other sheets.

take the help of debra's site
http://www.contextures.com/xladvfilter01.html.

the other one would be to code using Data Filter.

if you are able to use the advanced filter help on debra's site an
make this work, it will be great. else, revert, someone might be abl
to help you.

for the second part of your question, check this site:

http://peltiertech.com/Excel/Pivots/pivotstart.htm.

pivot table would be a good way to make that happen