Using Excel Worksheets like Access Queries
 

I have a workbook which contains an inventory of computers, and it works
well in a single worksheet - not very complicated. I'd like to have a 2nd
and 3rd worksheet within the same workbook which show a subset of rows from
the 1st worksheet, and are automatically kept in sync with changes to the
1st worksheet.

For instance, the inventory shows the user of each computer, but in some
cases that is 'Unknown' - I'd like all rows with an 'Unknown' owner to
appear in the 2nd worksheet. I also track computer make & model, and would
like all rows with a 'Dell' computer make to appear in the 3rd worksheet.
This type of functionality is easy in Access, using queries against a table.

Can this be done in Excel 2003?

Thanks much in advance.

Hi Lab Dude",

Excel is not a database. But it makes sense to use it like one, especially
when you have a small number of records.

What you want to do is usually accoplished with the VLOOKUP formula, or
Pivot Tables, or both. Depends on just what you want to do.

Some help on structuring "Excel Databases" here
http://edferrero.m6.net/DataTutor01.shtml

Ed Ferrero
http://edferrero.m6.net/


I have a workbook which contains an inventory of computers, and it works
well in a single worksheet - not very complicated. I'd like to have a 2nd
and 3rd worksheet within the same workbook which show a subset of rows
from the 1st worksheet, and are automatically kept in sync with changes to
the 1st worksheet.

For instance, the inventory shows the user of each computer, but in some
cases that is 'Unknown' - I'd like all rows with an 'Unknown' owner to
appear in the 2nd worksheet. I also track computer make & model, and
would like all rows with a 'Dell' computer make to appear in the 3rd
worksheet. This type of functionality is easy in Access, using queries
against a table.

Can this be done in Excel 2003?

Thanks much in advance.

I recommend you look at Data / Filter / AutoFilter
You can make extracts using AutoFilter followed by copy and paste
or by Data / Filter / Advanced Filter / Copy to another location
But don't try to keep the different lists in sync.
Just redo the filter when you need it.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup