Graham has attended:
Access Introduction course
Excel Subtotals
When I have summarised a report using subtotals is it possibe to copy and paste a collapsed view of the table without including the lowest level of detail
RE: Excel Subtotals
Hi Graham
Thanks for your question. I assume your question relates to subtotals in Excel because of the title, even though it's posted in the Access forum.
If you are using subtotals to create totals, then this would be possible, but you would need to apply an advanced filter first.
Because the totals are larger than other values in the column, you can do the following:
1. Apply the subtotals (Data - Subtotals)
2. Enter the heading of the column you have put the subtotals into in a cell up the top of the spreadsheet.
3. In the cell directly underneath this, enter >X where X is a number just smaller than the smallest total you have.
4. Go to Data - Filter - Advanced filter.
5. Choose Copy to another location, select the whole list of data for List range, select the two cells that you typed information into in steps 2 and 3 above. Next to Copy to, select a blank cell on the same sheet.
6. Click OK.
This should give you just the subtotals from the list, separate from the list itself. From there you can cut and paste this information onto another sheet if you wish.
I've attached an example at the bottom of this post.
Amanda