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.

--
"Live with cause and leave results to the great law of the universe. Pass
each day in peaceful contemplation." -Zengetsu

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" wrote:

> 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.
>
> --
> "Live with cause and leave results to the great law of the universe. Pass
> each day in peaceful contemplation." -Zengetsu
>
>

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).


On 11/10/06 9:31 AM, in article
04F4A5EF-821B-4353-A41E-(E-Mail Removed), "CLR"
<(E-Mail Removed)> wrote:

> 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" wrote:
>
>> 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.
>>
>> --
>> "Live with cause and leave results to the great law of the universe. Pass
>> each day in peaceful contemplation." -Zengetsu
>>
>>

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


Matthew Braun wrote:

> 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.
>
> --
> "Live with cause and leave results to the great law of the universe. Pass
> each day in peaceful contemplation." -Zengetsu

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



On 11/9/06 4:46 PM, in article C178FDCB.784%mjbraun@REMOVE_leydenjar.com,
"Matthew Braun" <mjbraun@REMOVE_leydenjar.com> wrote:

> 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,

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!


On 11/11/06 6:22 AM, in article
(E-Mail Removed), "Lori"
<(E-Mail Removed)> wrote:

> 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
>
>
> Matthew Braun wrote:
>
>> 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.
>>
>> --
>> "Live with cause and leave results to the great law of the universe. Pass
>> each day in peaceful contemplation." -Zengetsu
>

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!


On 11/11/06 3:22 PM, in article C17BA947.18631%(E-Mail Removed),
"CyberTaz" <(E-Mail Removed)> wrote:

> 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
>
>
>
> On 11/9/06 4:46 PM, in article C178FDCB.784%mjbraun@REMOVE_leydenjar.com,
> "Matthew Braun" <mjbraun@REMOVE_leydenjar.com> wrote:
>
>> 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.
>