Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Managing an Access Database from Excel | Excel Discussion (Misc queries) | |||
MS Access in Excel | Excel Worksheet Functions | |||
Refreshing Access data in Excel | Excel Discussion (Misc queries) | |||
Excel aficionado wants to learn Access | Excel Discussion (Misc queries) | |||
Excel aficionado wants to learn Access | Excel Discussion (Misc queries) |