viewing a subset of information from a large spreadsheet

[Candace]

I'm looking for a way to view a subset of information from a large
spreadsheet. Imagine a workbook of equipment for a business buiding. Each
room in the building has its own worksheet. Each room's worksheet contains a
list of equipment (scanner, printer, computer, telephone) with cells that
have a code assigned to each type of equipment. Here is an example
spreadsheet for office number one:

Code Item Ordered Working
------- --------- ------------ ----------
100 Scanner 12/1/2009 Yes
200 Printer 11/3/2007 No
300 Computer 1/22/2009 Yes
400 Telephone 10/8/2006 Yes

What would be the most efficient way to create a subset that shows a view of
all computers in the "whole building", based on the code that is assigned to
computers? So this view would show the rows from all worksheets in the
workbook that have a code of "100" based on the sample above. Any ideas?

 

[trip_to_tokyo]

Hi Candace, you ask for ideas so here are a couple:-

1. Does the Workbook need to be restructured?

2. Why does each room in the building need its own worksheet? Might this all
be better handled on one Worsheet and defining one column therein as Room.

3. What about Pivot Tables? These provide different views on your underlying
data and might give you what you want.

Just a few ideas that came to mind as I read through your posting.

Please hit Yes if my comments have helped.

Thanks.

 

[Otto Moehrbach]

Candace
Idea. Have a sheet that holds a drop-down cell. Click on that cell and
you will see a list of all the equipment, either the Code or the Item name,
your call. Click on one and, bingo, you have a listing of all of that code
number with whatever information you want, like
room/ordered/working/whatever. This listing could appear on the same sheet
as the drop-down or on any other sheet you wish. Does that sound like what
you want? HTH Otto