Get Subtotals in consecutive cells? 1

[as47]

Here's one I have struggled with.

I work in massive spreadsheets that require Subtotals. Getting the lists to subtotal is easy. However, I often need just the subtotals in a spreadsheet and nothing else.

More exactly, I need subtotal 1 in cell A1 and subtotal 2 in cell B1 and subtotal 3 in cell C1, and so on.

I've tried copying and pasting into another worksheet, I've tried copying and pasting just values into another worksheet. Both of these give me all the underlying data or dozens of cells between each subtotal.

Can anyone help me with this?

 

[AlexmacG]

I do not fully undersatand your problem but try in cell A1 etc. =subtotal(9,<range of data to be totalled>)
eg = subtotal(9,sheet2!a2:a9) [ for another sheets or = subtotal(9,c1:c99) in same sheet

 

[SkipVought]

as,

On the sheet where you want your subtotals, enter
[tt]
=subtotal(9,
[/tt]
then without hitting [Enter], select the sheet with the data and highlight the range to subtotal, then
[tt]
)
[/tt]
close the parentheses and hit [Enter]



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[as47]

Here's a better explanation of what I want to do.

On a single worksheet, I subtotal large amounts of data using Data>Subtotals. I end up with 30 to 50 subtotals inserted between every few dozen rows of source data.

The Data>Subtotals function allows me to collapse the spreadsheet to show ONLY the subtotals or to expand the spreadsheet to show the subtotals AND all the underlying information.

What I want to be able to do is (QUICKLY) take just the subtotals (collapsed- w/o the underlying info) and put them into another worksheet.

Right now, I am copying 1 subtotalled cell at a time and pasting it into a new worksheet until I get all the subtotals in a column into the new worksheet.

Doing this for 10 or 12 spreadsheets a day is VERY time consuming! If I can find FASTER way to do it, it would put me lightyears ahead!

 

[SkipVought]

Then use the PivotTable Wizard This can group by the same columns as SUBTOTAL.

Youj can also make the range dynamic using the OFFSET function faq68-1331 How can I rename a table as it changes size

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[as47]

That's a great idea. I hadn't thought to use a PT.
I will try it out!
Thanks!

 

[KenWright]

Whilst like Skip I also prefer a Pivot table for this, for what you want to do you can also just collapse so you have the view you want, select the data, do Edit / Go To / Visible cells only, then copy and paste where you want.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Oops - Edit / Go To / SPECIAL / Visble cells only

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

All KINDS of ways to skin a cat! ;-)

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244