"Executive Summary" report

[Matthew Braun]

I'm trying to set up an executive summary worksheet or report that would be
automatically (or semi-automatically) updated as the underlying data
changes. A simple example to illustrate my scenario:

Assume an "Employees" worksheet:

[Last Name][First Name]....[Status]

Where "Status" could be something like [Probation | Part Time | Full Time |
Contractor | Departed] (ensured using a validation list). Users would
adjust the status of the employees frequently, so the number of users in
each category changes.

Certain managers want a view of the relevant data, and they'd like a
worksheet that only shows Probation, Part, and Full Time employees formatted
underneath large headers, so from

"Employees"
[Last Name][First Name]....[Status]
Smith | Carol | ... | Part time
Jenkins | Leroy | ... | Contractor
Doe | John | ... | Full time
Public | John | ... | Probation


The "Executive Summary" sheet would display

***********FULL TIME************
John Doe

***********PART TIME************
Carol Smith

***********PROBATION************
John Public

And nothing more.

I cannot for the life of me figure out how to do this. Does anyone have any
pointers? (I'm using Excel 2004 from the Mac version, but PC or Mac doesn't
matter excessively). Pointers, hints, or suggestions would be great, as
none of the reporting tools I can find seem to provide this sort of
functionality.

 

[Guest]

I know of nothing in Excel to produce the report you desire in exactly that
format..........However, it could be done with a little sorting and copying
and pasteing........or with a custom macro.

hth
Vaya con Dios,
Chuck, CABGx3

 

[Matthew Braun]

Chuck,

Thank you for your response. I suspected as much: when I got to the point
that I was looking at using Query to pull the same data from an external
source, I started to fear that there was no built-in solution.

Macro writing it is (feh; pointy-clicky is so much easier).

I know of nothing in Excel to produce the report you desire in exactly that
format..........However, it could be done with a little sorting and copying
and pasteing........or with a custom macro.

hth
Vaya con Dios,
Chuck, CABGx3

I'm trying to set up an executive summary worksheet or report that would be
automatically (or semi-automatically) updated as the underlying data
changes. A simple example to illustrate my scenario:

Assume an "Employees" worksheet:

[Last Name][First Name]....[Status]

Where "Status" could be something like [Probation | Part Time | Full Time |
Contractor | Departed] (ensured using a validation list). Users would
adjust the status of the employees frequently, so the number of users in
each category changes.

Certain managers want a view of the relevant data, and they'd like a
worksheet that only shows Probation, Part, and Full Time employees formatted
underneath large headers, so from

"Employees"
[Last Name][First Name]....[Status]
Smith | Carol | ... | Part time
Jenkins | Leroy | ... | Contractor
Doe | John | ... | Full time
Public | John | ... | Probation


The "Executive Summary" sheet would display

***********FULL TIME************
John Doe

***********PART TIME************
Carol Smith

***********PROBATION************
John Public

And nothing more.

I cannot for the life of me figure out how to do this. Does anyone have any
pointers? (I'm using Excel 2004 from the Mac version, but PC or Mac doesn't
matter excessively). Pointers, hints, or suggestions would be great, as
none of the reporting tools I can find seem to provide this sort of
functionality.

 

[Lori]

You could try looking at pivot tables with outline formats by
experimenting with the built-in autoformats. It may not give you
exactly what you want but you might get close. For example something
like:

[Status] [Last Name] [First Name]

*Contractor*
Jenkins Leroy

*Full time*
Doe John

*Part time*
Smith Carol

*Probation*
Public John

 

[CyberTaz]

Another Option -

Take a look at the Subtotals feature in XL Help, then:

Sort Ascending on [Status], go to Data>Subtotals, select [Status] from the
'At each change in' list, choose Count from the 'Use function' list and
check [Status] in the 'Add subtotal to' list. After clicking OK you can
expand & collapse the list as you wish. You can also go back anytime to turn
the feature Off & On as you need.

HTH |:>)
Bob Jones
[MVP] Office:Mac

 

[Matthew Braun]

Lori,

Thank you for your response, though I'm not exactly sure I follow. Since
I've just begun studying pivot tables, I'm not sure how implement your
solution. My main concern is how I should lay out the pivot table (what do
you suggest I put into the page/row/column/data fields?).

Again, thank you for your help!

You could try looking at pivot tables with outline formats by
experimenting with the built-in autoformats. It may not give you
exactly what you want but you might get close. For example something
like:

[Status] [Last Name] [First Name]

*Contractor*
Jenkins Leroy

*Full time*
Doe John

*Part time*
Smith Carol

*Probation*
Public John

I'm trying to set up an executive summary worksheet or report that would be
automatically (or semi-automatically) updated as the underlying data
changes. A simple example to illustrate my scenario:

Assume an "Employees" worksheet:

[Last Name][First Name]....[Status]

Where "Status" could be something like [Probation | Part Time | Full Time |
Contractor | Departed] (ensured using a validation list). Users would
adjust the status of the employees frequently, so the number of users in
each category changes.

Certain managers want a view of the relevant data, and they'd like a
worksheet that only shows Probation, Part, and Full Time employees formatted
underneath large headers, so from

"Employees"
[Last Name][First Name]....[Status]
Smith | Carol | ... | Part time
Jenkins | Leroy | ... | Contractor
Doe | John | ... | Full time
Public | John | ... | Probation


The "Executive Summary" sheet would display

***********FULL TIME************
John Doe

***********PART TIME************
Carol Smith

***********PROBATION************
John Public

And nothing more.

I cannot for the life of me figure out how to do this. Does anyone have any
pointers? (I'm using Excel 2004 from the Mac version, but PC or Mac doesn't
matter excessively). Pointers, hints, or suggestions would be great, as
none of the reporting tools I can find seem to provide this sort of
functionality.

 

[Matthew Braun]

Bob,

Yours is a very useful solution and I'm sure that I will make use of it in
my other reports! I don't think it has the "pretty printing" that the
management want, but that's an eye candy issue that I will have to resolve.

Thank you!

Another Option -

Take a look at the Subtotals feature in XL Help, then:

Sort Ascending on [Status], go to Data>Subtotals, select [Status] from the
'At each change in' list, choose Count from the 'Use function' list and
check [Status] in the 'Add subtotal to' list. After clicking OK you can
expand & collapse the list as you wish. You can also go back anytime to turn
the feature Off & On as you need.

HTH |:>)
Bob Jones
[MVP] Office:Mac

I'm trying to set up an executive summary worksheet or report that would be
automatically (or semi-automatically) updated as the underlying data
changes. A simple example to illustrate my scenario:

Assume an "Employees" worksheet:

[Last Name][First Name]....[Status]

Where "Status" could be something like [Probation | Part Time | Full Time |
Contractor | Departed] (ensured using a validation list). Users would
adjust the status of the employees frequently, so the number of users in
each category changes.

Certain managers want a view of the relevant data, and they'd like a
worksheet that only shows Probation, Part, and Full Time employees formatted
underneath large headers, so from

"Employees"
[Last Name][First Name]....[Status]
Smith | Carol | ... | Part time
Jenkins | Leroy | ... | Contractor
Doe | John | ... | Full time
Public | John | ... | Probation


The "Executive Summary" sheet would display

***********FULL TIME************
John Doe

***********PART TIME************
Carol Smith

***********PROBATION************
John Public

And nothing more.

I cannot for the life of me figure out how to do this. Does anyone have any
pointers? (I'm using Excel 2004 from the Mac version, but PC or Mac doesn't
matter excessively). Pointers, hints, or suggestions would be great, as
none of the reporting tools I can find seem to provide this sort of
functionality.