Re: Manager Summaries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

One play ..

Sample construct at:
http://cjoint.com/?lslBHYRH7Y
AutoSortData_BySheetName.xls

Assume the source list is in ***: 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 *** 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 ***: 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 *** 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 *** 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
>
>


.

Quantcast