General discussion

Locked

Excel

By maryellen_nemeth ·
Is there a way that once you have created sub-totals, to collaps the worksheet displaying only the subtotals and then copying them (the sub-totals only) onto another worksheet or in another workbook?

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel

by Poettone In reply to Excel

I hope I understand correctly, you want a sheet with all the numbers and total's to be usable on another sheet but without the figures showing just the totals. If you can figure the numbers on one page you can easily copy the totals to another by cut and paste. If you need the numbers that you obtained you totals on to go along with the totals to the new sheet then you can just hide all the rows that have these numbers in them to only display the totals. then assign read only permissions to thenew sheet. I hope this has helped.Good Luck

Collapse -

Excel

by maryellen_nemeth In reply to Excel

The question was auto-closed by TechRepublic

Collapse -

Excel

by Peyison In reply to Excel

Check out the subtotals feature (Data - Subtotals...). If you have a list of data it will allow you to create subtotals, and then expand or collapse each section. The online help can provide more info.

good luck

Collapse -

Excel

by maryellen_nemeth In reply to Excel

The question was auto-closed by TechRepublic

Collapse -

Excel

by cmurphy In reply to Excel

Answer 1 is correct if your subtotal is already a value. However, your subtotal was probably figured using a formula where you would want to paste a link by copying the subtotal on your subtotal page and then select the cell in which you want to paste it on your total sheet (new sheet). Go to edit- paste special - paste link in this way any time you change something on your subtotal sheet, it will automaticly change on your total sheet. Hope this helps.

Collapse -

Excel

by maryellen_nemeth In reply to Excel

The question was auto-closed by TechRepublic

Collapse -

Excel

by SuzanneI In reply to Excel

The Subtotals feature might be the way to go (I haven't used it so I couldn't say), but barring that, a quick way to display only your subtotal rows and copy them would be to use the AutoFilter feature. This should work well enough if all of your subtotal rows have the word Subtotal in the heading column (or something else that singles them out from the rest).

To try this, select the range of cells that contains your data and your column and row headings. Select Filter>>AutoFilter from theData Menu. Small drop-down arrows will appear at the top of every column. Using the drop-down arrow, select Custom from the column that has your Subtotal headings. Select Contains in the top left box, and type "Subtotal" (without quotes) in the top right box. Click OK. This should filter your data so that only rows that contain the word "Subtotal" are still visible. Then you can copy these rows and paste them on another sheet. Only the visible rows will be copied, which is not the case if you were to just hide the intervening rows.

Hope this makes sense. Good luck.

Collapse -

Excel

by maryellen_nemeth In reply to Excel

The question was auto-closed by TechRepublic

Collapse -

Excel

by stan.fritz In reply to Excel

If I understand your question correctly, I think that once you have collapsed the rows to show only the totals (using the mouse to select the 2 on the left margin excel totaling menu) go back to the now visible subtotals, highlight the ones you wantto copy to the new sheet. Then (sorry, I use a pc, don't know if these instructions work on macs) press F5, mouse click to select SPECIAL from the GoTo pop-up menu then select VISIBLE CELLS ONLY from the GoTo Special pop-up menu, when you OK that option, the menu clears, your selected cells (even if they are not viewable on your screen now) can then be copied by pointing your mouse into that selected area and using the commonly invoked right click of the mouse and pasted into your new worksheetor workbook as you would any other "ordinary" data. I use this all the time, if it's too confusing, try practicing it a few times, it really works well for me and has saved a lot of time.

Collapse -

Excel

by maryellen_nemeth In reply to Excel

The question was auto-closed by TechRepublic

Back to Software Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums