how do I generate lists in worksheets based on a master list works

[Guest]

I've been struggling with this for a while now trying to figure this out on
my own. Having found no success I finally broke down and decided to ask for
help.

What I'd like to do is create worksheets that contain lists based on the
content of a master list on a separate worksheet. Let's say Sheet1 (the
master list) contains a four column list of classes, with corresponding room
numbers, teacher names, and start time. It might look something like this:

CLASS ROOM TEACHER START
Language 120 Stanley 900
Government 255 Hart 1020
Art 310 Bloom 1115
Math 255 Smith 1300
Science 310 Jones 1435
History 120 Bolt 1600

And then on Sheet2 I want to display all the classes held in Room 120 with
corresponding data from Sheet1, Sheet3 all the classes held in Room 255, and
Sheet4 all the classes held in room 310. Is there a way to write a formula
that will generate such lists in each of these sheets? I was thinking maybe
somehow using an array but I'm not sure how to got this to work properly. I
should also mention that I would need to have the lists on Sheet2, 3, and 4
be flexible enough that if the data grows or shrinks in terms of number of
entries that would correspond to those pages that those pages will grow and
shrink as well (in other words if I add five more classes to the master list
being held in room 120, Sheet1 will simply add them to the list it displays
and if I take two of the classes currently held in room 255, Sheet3 will
reflect that change as well).

In all honesty I'm not working with class lists but rather with long lists
of data generated by a project on which I'm currently working. I chose the
class list example to try to simplify my question. It still looks a bit
complex to me but hopefully you'll be able to read past that and still be
able to help me out. Thanks so much in advance!

 

[Sandy Mann]

If your real data sufficiently resembles your example, why not just use
Autofilter to filter the data in sheet 1 to show only what you want?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Hmm... interesting. Tell me more. What do I need to do in Sheet2 to
autofilter the list from Sheet1 to only show the classes listed in Room 120?
Thanks!

 

[Sandy Mann]

You misunderstand me. I meant that you simply autofilter the data in Sheet
1. Selecting Room 120 to filter on will then show only classes in that
room, no need to use any other sheets.

I assume that you know how to Autofilter.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Perhaps one formulas option which delivers it the way you want ..

Try this sample construct from my archives:
http://www.savefile.com/files/430142
AutoCopy Lines to Resp Sht Non Array.xls
(Full details inside, nicely rendered. Easy to adapt ..)

Data is continuously entered in a master ("parent") sheet, with lines neatly
auto-copied to each individual ("child") sheet based on the values within a
key col.

In the sample, the key col in the master sheet is the "State" col, which may
contain eg: NY, CA, NV, SD, AZ, etc. All lines with "NY" in the key col will
be auto-copied to the sheet named: NY, and appear neatly bunched at the top.
Ditto for lines with "CA", "NV", etc which will be copied into their
respective sheets.

Propagation of the "child" sheet is as simple as making a copy of the
initial one, then renaming it accordingly as the next key col value. Eg we
first formulate one child sheet for "NY", dress it up nicely, then just make
copies of the "NY" sheet, and rename these as: CA, NV, SD, etc.

In your case, the key col would be the "Room" (col B), eg: 120, 255, 310,
etc

---

 

[Guest]

I do know how to autofilter but that doesn't really address my original
question. I'm not looking to filter the data in sheet1 on sheet1. Sheet1
needs to remain the master list unfiltered. The intention would be to
distribute the file containing these sheets to a number of classrooms so they
can quickly look at a sheet that contains just their room's assigned classes
as well as browse the master list to look at different rooms simultaneously.
The reason it needs to be on multiple sheets is simply that the group
receiving the lists is unlikely to know how to do something even as simple as
autofilter. I suppose I could create an array on sheet2, 3 and 4 of
something like =Sheet1!A165536 and place it into columns A, B, C, and D and
then set the autofilters on those pages to exclude 0 and to only display rows
with the corresponding room number for a given sheet but that seems a bit
bloated. Is there another way to manage this?

 

[Guest]

Aha... that's what I was thinking. I think this will do it for me. Thanks!

 

[Max]

welcome ..

 

[Guest]

Try this:
In sheet2:
A2: holds 120

B2:
=IF(ISERR(SMALL(IF(ROOM=Sheet2!$A$2,ROW(INDIRECT("1:"&ROWS(CLASS)))),ROWS($1:1))),"",INDEX(CLASS,SMALL(IF(ROOM=Sheet2!$A$2,ROW(INDIRECT("1:"&ROWS(CLASS)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

 

[Ginetta]

What if each sheet linked back to the master sheet so all sheets would
basically have the exact same data.

Each sheet would then Autofilter the classroom Number column to only
show the values for that classroom.

Very easy, only requires that you reference back to the master sheet
and you
can duplicate the child sheets as much as you want and you have no
restrictions on how many rows of data you lookup (assuming you
reference
enough rows).

Just a thought.

N.

 

[Max]

Some points to note when you adapt the sample to suit ..

In the master sheet (WS1),

In K1 across, ensure that the unique room #s are entered as text, not as
numbers (just precede the entry with an apostrophe)

And if your key col B contains all numbers (instead of text), use this
revised formula instead in K2:
=IF($B2&""=K$1,ROW(),"")
Copy K2 across and fill down

The &"" part will convert the real number in B2 to a text number for
matching with the col headers in K1 across

---