Greetings (I'm sure this is easy, but I'm new to quite Excel)

I have a workbook with several sheets. WS1 has columns EmplNum, LName,
FName, Category, DateOfBirth, etc...
The Categories are driver, maintenance, cleaner, secretary, manager ....

I'd like WS2, 3, 4 etc to be automatically populated from WS1 by category.
Lets say WS2 is for drivers. It needs to show the EmplNum, LName, FName
of the drivers in the first three columns of WS2. Other columns will hold
info specific to drivers. When a new driver is added to WS1, say, he/she
needs to be updated on WS2. I can complete the missing entries in WS2 at a
later time.

WS3 would handle the cleaners, and so on.

Much thanks for any help offered
anny

Here's one play which automates it using non-array formulas ..

A sample construct is available at:
http://www.savefile.com/files/9963202
Auto-Filter_Data_To_Resp_Sheet_Non_Array_Approach.xls

In sheet: WS1 (the "master")

Assume data in cols A to E, headers in A1:E1,
data in row2 down, with the key col = col D (categories)

List the categories across in K1:O1,
viz.: Driver, Mtce, Cleaner, Secy, Mgr

Put in K2: =IF($D2=K$1,ROW(),"")
Copy across to O2, fill down to say, O100
to cover the max expected extent of data

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

Then, in a new sheet named: Driver
With the same col headers pasted into A1:E1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$O$1,0)),ROWS($A$1:A1
))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J, ,MATCH(WSN,WS1!$K$1:$O$1,0
)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$O$1,0)),0)))

Copy A2 across to E2, fill down to say, E10
(copy down by the smallest possible range sufficient to cover the max
expected extent for any category. Here, I've assumed that 9 rows (rows 2 to
10) is sufficient)

Cols A to E will return only the lines for cat: Driver from "WS1",
with all lines neatly bunched at the top

Now, we just need to make a copy of the sheet: Driver,
rename it as: Secy
and we'd get the results for cat: Secy

Repeat the copy rename sheet process
to get the rest of the category sheets (a one-time job)

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"anny" wrote in message
...
Greetings (I'm sure this is easy, but I'm new to quite Excel)

I have a workbook with several sheets. WS1 has columns EmplNum, LName,
FName, Category, DateOfBirth, etc...
The Categories are driver, maintenance, cleaner, secretary, manager ....

I'd like WS2, 3, 4 etc to be automatically populated from WS1 by
category.
Lets say WS2 is for drivers. It needs to show the EmplNum, LName, FName
of the drivers in the first three columns of WS2. Other columns will hold
info specific to drivers. When a new driver is added to WS1, say,
he/she
needs to be updated on WS2. I can complete the missing entries in WS2 at
a
later time.

WS3 would handle the cleaners, and so on.

Much thanks for any help offered
anny

I appreciate your detailed reply. I'm looking forward to trying it out.

anny

You're welcome, Anny.
Thanks for feeding back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bri" wrote in message
...
I appreciate your detailed reply. I'm looking forward to trying it out.

anny

Max

Your suggestion works VERY WELL! I'm new at excel so it took me quite a
while to work through the syntax, but I learned a lot. (getting there, but
slowly)

For my own education, could I ask two follow-up questions:

a) Is it possible to have the worksheets being populated from data in WS1
(Driver, Mtce ...) show rows that are automatically listed in order of, say,
EmplNum?

b) Is there an array approach that accomplishes the same thing as your
non-array approach?

anny
"Max" wrote in message
...
Here's one play which automates it using non-array formulas ..

A sample construct is available at:
http://www.savefile.com/files/9963202
Auto-Filter_Data_To_Resp_Sheet_Non_Array_Approach.xls

In sheet: WS1 (the "master")

Assume data in cols A to E, headers in A1:E1,
data in row2 down, with the key col = col D (categories)

List the categories across in K1:O1,
viz.: Driver, Mtce, Cleaner, Secy, Mgr

Put in K2: =IF($D2=K$1,ROW(),"")
Copy across to O2, fill down to say, O100
to cover the max expected extent of data

Click Insert Name Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box:
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")
))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came
from
a post by Harlan.

Then, in a new sheet named: Driver
With the same col headers pasted into A1:E1

Put in A2:
=IF(ISERROR(SMALL(OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$O$1,0)),ROWS($A$1:A1
))),"",INDEX(WS1!A:A,MATCH(SMALL(OFFSET(WS1!$J:$J, ,MATCH(WSN,WS1!$K$1:$O$1,0
)),ROWS($A$1:A1)),OFFSET(WS1!$J:$J,,MATCH(WSN,WS1! $K$1:$O$1,0)),0)))

Copy A2 across to E2, fill down to say, E10
(copy down by the smallest possible range sufficient to cover the max
expected extent for any category. Here, I've assumed that 9 rows (rows 2
to
10) is sufficient)

Cols A to E will return only the lines for cat: Driver from "WS1",
with all lines neatly bunched at the top

Now, we just need to make a copy of the sheet: Driver,
rename it as: Secy
and we'd get the results for cat: Secy

Repeat the copy rename sheet process
to get the rest of the category sheets (a one-time job)

Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"anny" wrote in message
...
Greetings (I'm sure this is easy, but I'm new to quite Excel)

I have a workbook with several sheets. WS1 has columns EmplNum, LName,
FName, Category, DateOfBirth, etc...
The Categories are driver, maintenance, cleaner, secretary, manager ....

I'd like WS2, 3, 4 etc to be automatically populated from WS1 by
category.
Lets say WS2 is for drivers. It needs to show the EmplNum, LName,
FName
of the drivers in the first three columns of WS2. Other columns will
hold
info specific to drivers. When a new driver is added to WS1, say,
he/she
needs to be updated on WS2. I can complete the missing entries in WS2 at
a
later time.

WS3 would handle the cleaners, and so on.

Much thanks for any help offered
anny

"anny" wrote:
.. Your suggestion works VERY WELL! ..

Glad to hear that !

a) Is it possible to have the worksheets being populated from data in WS1
(Driver, Mtce ...) show rows that are automatically listed in order of,
say,
EmplNum?

Auto-Ascending Sort by EmplNum:
No prob, as-is, just a slight tweak to the criteria formula in the previous
set-up will return an ascending sort by the EmplNum (EmplNum is in col A).

Just change the criteria formula in WS1's anchor cell K2 to:
=IF($D2=K$1,$A2,""), then fill across and down as before.
No change is needed to the formulas in Driver, Mtce, Secy, etc.

The above of course assumes that the EmplNum issued for each category is
unique per employee, which usually should be the case.

If it isn't, a slightly more complex criteria incorporating an arbitrary
tie-breaker would be required, viz. we could use instead in K2:
=IF($D2=K$1,$A2+ROW()/10^10,""), filled across and down.

And as before, no change is required to the formulas in Driver, Mtce, Secy,
etc

Auto-Descending Sort by EmplNum:
To auto-return a *descending* sort by EmplNum in: Driver, Secy, etc

a. If EmplNum is unique per employee per category,

1. Use the criteria in WS1's anchor cell K2:
=IF($D2=K$1,$A2,""), fill across/down

2. Change the SMALL to LARGE in the formula in the anchor cell A2 in each of
the cat's sheets: Driver, Secy, etc, and fill across/down (to the smallest
extent, remember<g). To change, just select A2 in Driver (say), and click
Edit Replace to find: SMALL, replace with: LARGE Replace, Close. Then
re-select A2 and fill across/down.

b. If EmplNum is *not unique* per employee per category (i.e. there could be
duplicate EmplNum [inadvertently?] issued in any category, eg: 2 or more
drivers having the same EmplNum ??),

1. Use the slightly more complex criteria in WS1's anchor cell K2:
=IF($D2=K$1,$A2-ROW()/10^10,"")

The formula's similar to the one above for ascending sort, except that here,
we *subtract* the tie-breaker portion [ROW()/10^10] from the EmplNum instead
of adding

2. Change the SMALL to LARGE in the formula in the anchor cell A2 in each of
the cat's sheets: Driver, Secy, etc, and fill across/down, as before

b) Is there an array approach that accomplishes the same thing as your
non-array approach?

Very possibly so <g, especially one which satisfies your original query,
w/o the add-on complexities of having auto-ascending/descending sorts and
catering for possibility of duplicates in the sort key col A, EmplNum.

Hang around awhile for others much better versed in complex arrays to come
along and share their insights here. I'll also try to post an array option
(adapted from a Bob Phillips' post) for your original query a bit later
here.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

b) Is there an array approach that accomplishes the same thing
as your non-array approach?
....
... I'll also try to post an array option (adapted from a Bob Phillips'
post)
for your original query a bit later here.

As promised, here's an array approach to your original query:
http://www.savefile.com/files/1955351
Auto-Filter Data To Resp Sheet_Array Approach.xls

The set-up's similar to the previous non-array approach. We still use the
defined name: WSN (from a post by Harlan). The array formula assumes a
"hard coded" source data extent in the "mastersheet" WS1 within rows 2 - 100
(range can be adapted to suit). The prime benefit of course, is that it
does away with the criteria cols K to O in WS1.

In a sheet named: Driver
(col headers pasted into A1:E1)

Put in A2, array-enter the formula (press CTRL+SHIFT+ENTER):
=IF(ROW()-ROW(A$2:A$100)+1ROWS(WS1!$A$2:$A$100)-COUNTIF(WS1!$D$2:$D$100,"<
"&WSN),"",INDIRECT("WS1!"&ADDRESS(SMALL((IF(WS1!$D $2:$D$100=WSN,ROW(WS1!$D$2
:$D$100),ROW()+ROWS(WS1!$D$2:$D$100))),ROW()-ROW(A$2:A$100)+1),COLUMN(WS1!A$
2:A$100),4)))

Copy A2 across to E2, fill down to say, E10
(As before, copy down by the smallest possible range sufficient
to cover the max expected extent for any category.)

For a neater look, we can suppress extraneous zeros from showing in the
sheet via: Tools Options View tab Uncheck "Zero values" OK.
Otherwise we'll see a lot of zeros showing in the empty lines.

Then just duplicate "Driver", and rename it to Secy, Mgr, etc, as before
And that should do it neat and nice.

As to the further requirements of :

.. the add-on complexities of having auto-ascending/descending sorts and
catering for possibility of duplicates in the sort key col A, EmplNum.

I'm not sure whether it is possible with a single complex array, or if so,
how the complex array above could be amended further. Hang around awhile
for possible insights from others better versed in this.

Notwithstanding the need for helper cols, I'd prefer the much simpler
non-array approach where we can focus on getting the correct "extract"
criteria up w/o worrying about integrating the criteria with the neat
churning out of the results in the destination sheets when we fill the
formulae across/down. The non-array approach is simpler to
understand/explain, debug and cross-apply to other situations.

Either way, in "real" application, due to the intensive calcs involved, I'd
usually set the calc mode to Manual (via: Tools Options Calculation
tab), and press F9 to recalc only when necessary (keep performance
tolerable).

Recollect Roger Govier also posted an alternative using Pivot Tables (w/o
any formulae), in his response to a similar query recently:
http://tinyurl.com/ddutm

(Roger's sample file in the cjoint link therein has expired since.
But .. he might re-post a fresh link to the sample if he reads this <g)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

... Roger Govier also posted an alternative using Pivot Tables (w/o
any formulae), in his response to a similar query recently:
http://tinyurl.com/ddutm

(Roger's sample file in the cjoint link therein has expired since ..

Managed to locate my d/l of Roger's sample ..

Here's a fresh link to Roger's sample, for those interested:
http://www.savefile.com/files/1939101
Auto-Filter Data To Resp Sheet_Pivot Table Approach_RogerGovier.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---