How Do I Run a Query on Internal Data?

[TC]

I'm using Excel 2000. I have a worksheet with a table of data on
Sheet1. I'd like Sheet2 to contain that same table sorted in a specific
way. If I make changes to Sheet1, I'd like Sheet2 to be updated
automatically (or at least whenever I recalculate manually).

I'm a database guy, so I understand this as a query. Sheet2 should
contain a query which acts on the table in Sheet1. I can't get the
Excel query feature to work, however. Excel seems to know how to run a
query on external data, but not on data from within the same worksheet.

Can anyone offer advice? Does Excel have the ability to do what I want?
If so, does it do it through the "query" feature or through some other
feature?


-TC

 

[Earl Kiosterud]

TC,

Data - Filter - Advanced filter is effectively Excel's internal query. It's
procedural -- to make it automatic, you'll have to record a macro (and hang
on to the criteria range). Use the "Copy to another location" to get the
data set to the second sheet.

Depending on your requirements, you may want to filter the table in place.
FOr that, you can also use Data - Filter - Advanced filter, or in-place ad
hoc filtering with Autofilter.

 

[Lisa B.]

In Excel 2002, you can query the data in your current workbook in exactly
the same way that you would query data in an external workbook.

"Advanced Filter" is an option, but as mentioned it's not automatic...and
you probably can't change the column order around to your liking...and it
probably can't be set up to automatically fill down formulas in adjacent
columns...and etc.

Lisa B.