Consolidate tables/ranges with text from multiple worksheets

[coxa]

I have 8 worksheets each with a single table. All the tables have the same
column headings and have less than 600 rows. Data is primarily text such as
a category name and description. I have unique ID's for every line of data.
I want to be able to have a single sheet where I can show all the rows of
information according to criteria such as category. I realize that the
easiest solution is to put it in Access, but not all of the people who will
be using this are familiar with Access. They want it in Excel.
I tried consolidating multiple ranges in a Pivot table, but apparently they
can't show text because you have to utilize a numeric function. A single
worksheet pivot table might work if there was a way to consolidate all the
tables (or ranges) onto a a single worksheet that would be updated when the
individual worksheets are updated. Any ideas?

 

[ShaneDevenshire]

hi,

Easiest way would be to combine all the sheets into one sheet and then use
an autofilter against the combined data.

It is possible to use a database query or a pivot table to do this but you
don't want to do that, its far to complex especially with 8 worksheets.