 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2007Subtotal Report: Creating new sheet(tab) for each group?
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 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...  SignatureHTH... Jim Thomlinson > Good day -- > I used the subtotal functions to generate the following report. [quoted text clipped - 25 lines] > Steve Brown Total 5,400 > Grand Total 38,500 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 On Jan 13, 2:19 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-This-.com> wrote: > 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 [quoted text clipped - 27 lines] > > John Smith May Metal Desk 3,100 > > John Smith Total 18,800 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. > Jim -- > That was so simple it was embarrassing :-) [quoted text clipped - 40 lines] >>>John Smith May Metal Desk 3,100 >>>John Smith Total 18,800 SignatureDebra Dalgleish Contextures http://www.contextures.com/tiptech.html
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.