Subtotal Report: Creating new sheet(tab) for each group?

[DavidJ]

Good day --
I used the subtotal functions to generate the following report.
What I would to instead of having to insert page break of each group,
is have it generate a new worksheet for each group, and change to tab
name to sales person name?

Any ideas, suggestions, links would be appreciated?

David




Sales Person Month ProdName SalesValue
John Cage Jan Metal Desk 3,100
John Cage Mar Wood Chair 4,500
John Cage Total 7,600

John Smith Jan Wood Chair 4,500
John Smith Feb Metal Chair 1,300
John Smith Mar Wood Desk 5,400
John Smith Apr Wood Chair 4,500
John Smith May Metal Desk 3,100
John Smith Total 18,800

Mary Davis Jan Wood Desk 5,400
Mary Davis Feb Metal Chair 1,300
Mary Davis Total 6,700

Steve Brown Mar Wood Desk 5,400
Steve Brown Total 5,400
Grand Total 38,500

 

[Guest]

Instead of doing a subtotal try using a pivot table. Pivot tables will do
your subtotaling for you and through a feature called show pages the pivot
table can spawn a seperate page for each value in the filter section (Drop
the sales person in the top section fo the pivot). It will also name the
sheet tabs it creates...

 

[DavidJ]

Jim --
That was so simple it was embarrassing
Can I have it generate separate (linked) workbook for each sales
person?
If I disable drilldown will that keep John Cage from using the "drop
down list" to view Mary Smiths Sales data in his spreadsheet?

Thanks again,
David

 

[Debra Dalgleish]

If you disable drill to details, users won't be able to double-click on
a cell to see the underlying records.

You could protect the sheet, and not allow users to use PivotTables, but
that would disable all the dropdowns, not just the ones in the Page
area. Also, Excel worksheet protection is easily broken, and they may
unprotect the sheet and view the data.

If you want each user to have a working pivot table, with only their
data, you can use an Advanced Filter to extract their data from the main
table, into a new workbook. Then, create a pivot table from each
person's data. There are instructions here for Advanced Filter:

http://www.contextures.com/xladvfilter01.html

There's a sample file here that creates a worksheet with each person's data:

http://www.contextures.com/excelfiles.html

Under Filters, look for 'FL0009 - Update Sheets from Master'. You could
modify it to create each list in a separate workbook.