Re: Manager Summaries
- From: "Max" <demechanik@xxxxxxxxx>
- Date: Fri, 18 Nov 2005 18:29:28 +0800
One play ..
Sample construct at:
http://cjoint.com/?lslBHYRH7Y
AutoSortData_BySheetName.xls
Assume the source list is in sheet: Master
in cols A to B, headers in row1, data from row2 down
Using empty cols to the right of the data, say cols K onwards
List the Mgr's names in K1, L1 across: Joe Bloggs, Bill Gatling, etc
Put in K2: =IF($A2=K$1,ROW(),"")
Copy K2 across to L2, fill down to say L10,
to cover the max expected data in the master list
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)
In a new sheet named: Joe Bloggs
With the same col headers in A1:B1
Put in A2:
=IF(ISERROR(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,Master!$K$1:$IV$1,0)),ROWS(
$A$1:A1))),"",INDEX(Master!A:A,MATCH(SMALL(OFFSET(Master!$J:$J,,MATCH(WSN,Ma
ster!$K$1:$IV$1,0)),ROWS($A$1:A1)),OFFSET(Master!$J:$J,,MATCH(WSN,Master!$K$
1:$IV$1,0)),0)))
Copy A2 across to B2, fill down to B10
(cover the same range size as was done in "Master"' cols K, L)
Cols A to B will return only the lines for the mgr: Joe Bloggs from
"Master", all neatly bunched at the top
Now, just make a copy of the sheet: Joe Bloggs, rename it as: Bill Gatling,
and you'd get the results for Bill Gatling. Repeat as required to get the
lists for all the other Mgrs.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"John Ortt" <johnortt@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:437d99cc_1@xxxxxxxxxxxxxxxxxxxxxxxxx
> Hi everyone,
>
> I have a single sheet with a list of manager's names and their employees
in
> the folowing format
>
> Manager Employee
> Joe Bloggs Fred Flintstone
> Bill Gates Goeff Capes
> Bill Gates Barney Rubble
> Joe Bloggs Dan Dare
>
> I would like a separate sheet for each manager which just pulls out their
> employees, ie:
>
> Manager Employee
> Joe Bloggs Fred Flintstone
> Joe Bloggs Dan Dare
>
> I can do it but only with blank gaps where the other managers names go.
Is
> it possible using cell formulas to get them all to be displayed on
> consecutive lines?
>
> Hope that makes sense and thx in advance.
>
> John
>
>
.
- References:
- Manager Summaries
- From: John Ortt
- Manager Summaries
- Prev by Date: Re: comparing two rows, then highting?
- Next by Date: Numbering
- Previous by thread: Manager Summaries
- Next by thread: Numbering
- Index(es):
Relevant Pages
|
