Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Lab Dude
 
Posts: n/a
Default 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   Report Post  
Ed Ferrero
 
Posts: n/a
Default

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   Report Post  
Bill Manville
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Managing an Access Database from Excel Kostis Vezeridis Excel Discussion (Misc queries) 2 February 11th 05 06:21 PM
MS Access in Excel Jackie Excel Worksheet Functions 4 February 11th 05 03:21 PM
Refreshing Access data in Excel Jimmy G Excel Discussion (Misc queries) 0 December 21st 04 04:53 PM
Excel aficionado wants to learn Access Hari Excel Discussion (Misc queries) 0 December 3rd 04 07:03 AM
Excel aficionado wants to learn Access Hari Excel Discussion (Misc queries) 0 December 3rd 04 06:47 AM


All times are GMT +1. The time now is 01:30 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"