Collapse subtotals?

Using WindowsXP, Excel 2002

After running the Subtotal command, with subtotals showing below the
cells that were summed, the subtotal appears in bold as "1201 Total"
and the amount of the subtotal.  Next is "1202 Total," and so on.

Is there a slick way to then collapse the list so that only a list of
subtotals information is displayed?

Thanks,
0
ed9369 (20)
2/20/2009 3:02:56 AM
excel 39880 articles. 2 followers. Follow

6 Replies
418 Views

Similar Articles

[PageSpeed] 33

Look at the outlining symbols to the left of the worksheet window.

You'll see those +'s and -'s that expand/contract particular groups.

Look up to the top of those outlining symbols and you'll see 1, 2, 3, ...

These "buttons" can be clicked to show or hide levels.

If you don't see those outlining symbols, check:
Tools|Options|View tab|check outlining symbols.

The Hun wrote:
> 
> Using WindowsXP, Excel 2002
> 
> After running the Subtotal command, with subtotals showing below the
> cells that were summed, the subtotal appears in bold as "1201 Total"
> and the amount of the subtotal.  Next is "1202 Total," and so on.
> 
> Is there a slick way to then collapse the list so that only a list of
> subtotals information is displayed?
> 
> Thanks,

-- 

Dave Peterson
0
petersod (12003)
2/20/2009 3:21:50 AM
On Feb 19, 7:21=A0pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> Look at the outlining symbols to the left of the worksheet window.
>
> You'll see those +'s and -'s that expand/contract particular groups.
>
> Look up to the top of those outlining symbols and you'll see 1, 2, 3, ...
>
> These "buttons" can be clicked to show or hide levels.
>
> If you don't see those outlining symbols, check:
> Tools|Options|View tab|check outlining symbols.
>
> The Hun wrote:
>
> > Using WindowsXP, Excel 2002
>
> > After running the Subtotal command, with subtotals showing below the
> > cells that were summed, the subtotal appears in bold as "1201 Total"
> > and the amount of the subtotal. =A0Next is "1202 Total," and so on.
>
> > Is there a slick way to then collapse the list so that only a list of
> > subtotals information is displayed?
>
> > Thanks,
>
> --
>
> Dave Peterson

Excellent and clearly stated reply.  Thank you.

But there's a wrinkle in my problem that I didn't realize till now.
Even though Outlining Symbols is checked in Tools / Options, they
aren't displayed.  This is why:

Col A has entries for projects:  "Red," "White," Blue."
Col B has entries for account numbers:  1201, 1202, 1203...and so
on...
Col C has entries for various dollar amounts.

1.  I first do a Sort on Col A to group all entries by color.
2.  I then Filter the list to choose only "Red," (or "White" or
"Blue").
3.  I then do a Subtotal on that Filtered "Red" list.

The Outlining Symbols aren't displayed.

The Outlining Symbols will only display when Subtotal is run on the
whole list -- a list that has not been Filtered.

Is there a work-around for this?

I'd like to be able see the collapsed Subtotals of the Filtered list.
Maybe there's a whole different approach that I'm not thinking about
to get the information I want.

Thanks,
0
ed9369 (20)
2/20/2009 3:59:16 PM
First, I would never use data|subtotals and data|Filter|autofilter on the same
range.

The formulas inserted by =subtotal() ignore the rows hidden by the autofilter. 
I think it's better to drop the autofilter and just use the subtotals.



The Hun wrote:
> 
> On Feb 19, 7:21 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > Look at the outlining symbols to the left of the worksheet window.
> >
> > You'll see those +'s and -'s that expand/contract particular groups.
> >
> > Look up to the top of those outlining symbols and you'll see 1, 2, 3, ...
> >
> > These "buttons" can be clicked to show or hide levels.
> >
> > If you don't see those outlining symbols, check:
> > Tools|Options|View tab|check outlining symbols.
> >
> > The Hun wrote:
> >
> > > Using WindowsXP, Excel 2002
> >
> > > After running the Subtotal command, with subtotals showing below the
> > > cells that were summed, the subtotal appears in bold as "1201 Total"
> > > and the amount of the subtotal.  Next is "1202 Total," and so on.
> >
> > > Is there a slick way to then collapse the list so that only a list of
> > > subtotals information is displayed?
> >
> > > Thanks,
> >
> > --
> >
> > Dave Peterson
> 
> Excellent and clearly stated reply.  Thank you.
> 
> But there's a wrinkle in my problem that I didn't realize till now.
> Even though Outlining Symbols is checked in Tools / Options, they
> aren't displayed.  This is why:
> 
> Col A has entries for projects:  "Red," "White," Blue."
> Col B has entries for account numbers:  1201, 1202, 1203...and so
> on...
> Col C has entries for various dollar amounts.
> 
> 1.  I first do a Sort on Col A to group all entries by color.
> 2.  I then Filter the list to choose only "Red," (or "White" or
> "Blue").
> 3.  I then do a Subtotal on that Filtered "Red" list.
> 
> The Outlining Symbols aren't displayed.
> 
> The Outlining Symbols will only display when Subtotal is run on the
> whole list -- a list that has not been Filtered.
> 
> Is there a work-around for this?
> 
> I'd like to be able see the collapsed Subtotals of the Filtered list.
> Maybe there's a whole different approach that I'm not thinking about
> to get the information I want.
> 
> Thanks,

-- 

Dave Peterson
0
petersod (12003)
2/20/2009 5:22:39 PM
On Feb 20, 9:22=A0am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> First, I would never use data|subtotals and data|Filter|autofilter on the=
 same
> range.
>
> The formulas inserted by =3Dsubtotal() ignore the rows hidden by the auto=
filter.
> I think it's better to drop the autofilter and just use the subtotals.
>
>
>
> The Hun wrote:
>
> > On Feb 19, 7:21 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > Look at the outlining symbols to the left of the worksheet window.
>
> > > You'll see those +'s and -'s that expand/contract particular groups.
>
> > > Look up to the top of those outlining symbols and you'll see 1, 2, 3,=
 ...
>
> > > These "buttons" can be clicked to show or hide levels.
>
> > > If you don't see those outlining symbols, check:
> > > Tools|Options|View tab|check outlining symbols.
>
> > > The Hun wrote:
>
> > > > Using WindowsXP, Excel 2002
>
> > > > After running the Subtotal command, with subtotals showing below th=
e
> > > > cells that were summed, the subtotal appears in bold as "1201 Total=
"
> > > > and the amount of the subtotal. =A0Next is "1202 Total," and so on.
>
> > > > Is there a slick way to then collapse the list so that only a list =
of
> > > > subtotals information is displayed?
>
> > > > Thanks,
>
> > > --
>
> > > Dave Peterson
>
> > Excellent and clearly stated reply. =A0Thank you.
>
> > But there's a wrinkle in my problem that I didn't realize till now.
> > Even though Outlining Symbols is checked in Tools / Options, they
> > aren't displayed. =A0This is why:
>
> > Col A has entries for projects: =A0"Red," "White," Blue."
> > Col B has entries for account numbers: =A01201, 1202, 1203...and so
> > on...
> > Col C has entries for various dollar amounts.
>
> > 1. =A0I first do a Sort on Col A to group all entries by color.
> > 2. =A0I then Filter the list to choose only "Red," (or "White" or
> > "Blue").
> > 3. =A0I then do a Subtotal on that Filtered "Red" list.
>
> > The Outlining Symbols aren't displayed.
>
> > The Outlining Symbols will only display when Subtotal is run on the
> > whole list -- a list that has not been Filtered.
>
> > Is there a work-around for this?
>
> > I'd like to be able see the collapsed Subtotals of the Filtered list.
> > Maybe there's a whole different approach that I'm not thinking about
> > to get the information I want.
>
> > Thanks,
>
> --
>
> Dave Peterson


Thanks.  I would like to be able to not filter the list, but for each
group -- Red, White, Blue -- the account numbers repeat, meaning that
Red will have entries for 1201, 1202, 1203 and so on, and White and
Blue will also have entries for 1201, 1202, 1203 and so on.

For me to get Subtotals of only Red 1201, 1202 and so on, I have to
filter out White and Blue so that their entries for 1201, 1202, 1203
are not included in the Subtotal.

I'm not trying to use this as a substitute for full-on accounting,
just to track some daily expenses very quickly and have the basic info
without having to wait 7 to 10 days for the accountant to give me the
info, which by then is far too late.  I just want to get some basic
subtotals very quickly, daily.

It sounds like I've reached a limitation in Excel, and that I'll have
to separate Red, White and Blue without relying on Filter to do it.

Maybe the best work-around is this:
1.  Separate Red, White and Blue with a Sort,
2.  then insert a new Header Row for each group,
3.  then do a Subtotal for each group separately, one at a time

Outline Symbols will then appear for each group, separately, one at a
time.

Again, thanks for your replies.  I appreciate the fact that you didn't
just say "go to this link," or "do it with a ____(whatever the command
is)____."  Half the time, those links don't address the question and
simply waste everyone's effort, and the "whatever command" type of
reply usually leaves us asking WHERE that command, is and HOW we begin
using it.

Thanks,

0
ed9369 (20)
2/20/2009 6:39:23 PM
You may want to look at pivottables.  If you're just looking for summaries, they
may be what you want.

And if I have a class of data I want to exclude, I'll often add a helper column
that evaluates to Keep or Don't Keep.

Then I can sort/filter/pivot (as a page field) by that helper column.

And even if you don't like links, here are a bunch about pivottables.

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

The Hun wrote:
> 
> On Feb 20, 9:22 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > First, I would never use data|subtotals and data|Filter|autofilter on the same
> > range.
> >
> > The formulas inserted by =subtotal() ignore the rows hidden by the autofilter.
> > I think it's better to drop the autofilter and just use the subtotals.
> >
> >
> >
> > The Hun wrote:
> >
> > > On Feb 19, 7:21 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > Look at the outlining symbols to the left of the worksheet window.
> >
> > > > You'll see those +'s and -'s that expand/contract particular groups.
> >
> > > > Look up to the top of those outlining symbols and you'll see 1, 2, 3, ...
> >
> > > > These "buttons" can be clicked to show or hide levels.
> >
> > > > If you don't see those outlining symbols, check:
> > > > Tools|Options|View tab|check outlining symbols.
> >
> > > > The Hun wrote:
> >
> > > > > Using WindowsXP, Excel 2002
> >
> > > > > After running the Subtotal command, with subtotals showing below the
> > > > > cells that were summed, the subtotal appears in bold as "1201 Total"
> > > > > and the amount of the subtotal.  Next is "1202 Total," and so on.
> >
> > > > > Is there a slick way to then collapse the list so that only a list of
> > > > > subtotals information is displayed?
> >
> > > > > Thanks,
> >
> > > > --
> >
> > > > Dave Peterson
> >
> > > Excellent and clearly stated reply.  Thank you.
> >
> > > But there's a wrinkle in my problem that I didn't realize till now.
> > > Even though Outlining Symbols is checked in Tools / Options, they
> > > aren't displayed.  This is why:
> >
> > > Col A has entries for projects:  "Red," "White," Blue."
> > > Col B has entries for account numbers:  1201, 1202, 1203...and so
> > > on...
> > > Col C has entries for various dollar amounts.
> >
> > > 1.  I first do a Sort on Col A to group all entries by color.
> > > 2.  I then Filter the list to choose only "Red," (or "White" or
> > > "Blue").
> > > 3.  I then do a Subtotal on that Filtered "Red" list.
> >
> > > The Outlining Symbols aren't displayed.
> >
> > > The Outlining Symbols will only display when Subtotal is run on the
> > > whole list -- a list that has not been Filtered.
> >
> > > Is there a work-around for this?
> >
> > > I'd like to be able see the collapsed Subtotals of the Filtered list.
> > > Maybe there's a whole different approach that I'm not thinking about
> > > to get the information I want.
> >
> > > Thanks,
> >
> > --
> >
> > Dave Peterson
> 
> Thanks.  I would like to be able to not filter the list, but for each
> group -- Red, White, Blue -- the account numbers repeat, meaning that
> Red will have entries for 1201, 1202, 1203 and so on, and White and
> Blue will also have entries for 1201, 1202, 1203 and so on.
> 
> For me to get Subtotals of only Red 1201, 1202 and so on, I have to
> filter out White and Blue so that their entries for 1201, 1202, 1203
> are not included in the Subtotal.
> 
> I'm not trying to use this as a substitute for full-on accounting,
> just to track some daily expenses very quickly and have the basic info
> without having to wait 7 to 10 days for the accountant to give me the
> info, which by then is far too late.  I just want to get some basic
> subtotals very quickly, daily.
> 
> It sounds like I've reached a limitation in Excel, and that I'll have
> to separate Red, White and Blue without relying on Filter to do it.
> 
> Maybe the best work-around is this:
> 1.  Separate Red, White and Blue with a Sort,
> 2.  then insert a new Header Row for each group,
> 3.  then do a Subtotal for each group separately, one at a time
> 
> Outline Symbols will then appear for each group, separately, one at a
> time.
> 
> Again, thanks for your replies.  I appreciate the fact that you didn't
> just say "go to this link," or "do it with a ____(whatever the command
> is)____."  Half the time, those links don't address the question and
> simply waste everyone's effort, and the "whatever command" type of
> reply usually leaves us asking WHERE that command, is and HOW we begin
> using it.
> 
> Thanks,

-- 

Dave Peterson
0
petersod (12003)
2/20/2009 7:16:10 PM
I had some luck with the following procedure.

Insert a column just to the right of the Column B.  Type the formula A1&B1.  
This will create Red1201, Red1202 Blue1201, etc.  Now when you do Data 
Subtotals select the new column for the "At each change in" and you should 
have what you are looking for.
-- 
Frank K


"Dave Peterson" wrote:

> You may want to look at pivottables.  If you're just looking for summaries, they
> may be what you want.
> 
> And if I have a class of data I want to exclude, I'll often add a helper column
> that evaluates to Keep or Don't Keep.
> 
> Then I can sort/filter/pivot (as a page field) by that helper column.
> 
> And even if you don't like links, here are a bunch about pivottables.
> 
> Debra Dalgleish's pictures at Jon Peltier's site:
> http://peltiertech.com/Excel/Pivots/pivottables.htm
> And Debra's own site:
> http://www.contextures.com/xlPivot01.html
> 
> John Walkenbach also has some at:
> http://j-walk.com/ss/excel/files/general.htm
> (look for Tony Gwynn's Hit Database)
> 
> Chip Pearson keeps Harald Staff's notes at:
> http://www.cpearson.com/excel/pivots.htm
> 
> MS has some at (xl2000 and xl2002):
> http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
> 
> The Hun wrote:
> > 
> > On Feb 20, 9:22 am, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > First, I would never use data|subtotals and data|Filter|autofilter on the same
> > > range.
> > >
> > > The formulas inserted by =subtotal() ignore the rows hidden by the autofilter.
> > > I think it's better to drop the autofilter and just use the subtotals.
> > >
> > >
> > >
> > > The Hun wrote:
> > >
> > > > On Feb 19, 7:21 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > > > Look at the outlining symbols to the left of the worksheet window.
> > >
> > > > > You'll see those +'s and -'s that expand/contract particular groups.
> > >
> > > > > Look up to the top of those outlining symbols and you'll see 1, 2, 3, ...
> > >
> > > > > These "buttons" can be clicked to show or hide levels.
> > >
> > > > > If you don't see those outlining symbols, check:
> > > > > Tools|Options|View tab|check outlining symbols.
> > >
> > > > > The Hun wrote:
> > >
> > > > > > Using WindowsXP, Excel 2002
> > >
> > > > > > After running the Subtotal command, with subtotals showing below the
> > > > > > cells that were summed, the subtotal appears in bold as "1201 Total"
> > > > > > and the amount of the subtotal.  Next is "1202 Total," and so on.
> > >
> > > > > > Is there a slick way to then collapse the list so that only a list of
> > > > > > subtotals information is displayed?
> > >
> > > > > > Thanks,
> > >
> > > > > --
> > >
> > > > > Dave Peterson
> > >
> > > > Excellent and clearly stated reply.  Thank you.
> > >
> > > > But there's a wrinkle in my problem that I didn't realize till now.
> > > > Even though Outlining Symbols is checked in Tools / Options, they
> > > > aren't displayed.  This is why:
> > >
> > > > Col A has entries for projects:  "Red," "White," Blue."
> > > > Col B has entries for account numbers:  1201, 1202, 1203...and so
> > > > on...
> > > > Col C has entries for various dollar amounts.
> > >
> > > > 1.  I first do a Sort on Col A to group all entries by color.
> > > > 2.  I then Filter the list to choose only "Red," (or "White" or
> > > > "Blue").
> > > > 3.  I then do a Subtotal on that Filtered "Red" list.
> > >
> > > > The Outlining Symbols aren't displayed.
> > >
> > > > The Outlining Symbols will only display when Subtotal is run on the
> > > > whole list -- a list that has not been Filtered.
> > >
> > > > Is there a work-around for this?
> > >
> > > > I'd like to be able see the collapsed Subtotals of the Filtered list.
> > > > Maybe there's a whole different approach that I'm not thinking about
> > > > to get the information I want.
> > >
> > > > Thanks,
> > >
> > > --
> > >
> > > Dave Peterson
> > 
> > Thanks.  I would like to be able to not filter the list, but for each
> > group -- Red, White, Blue -- the account numbers repeat, meaning that
> > Red will have entries for 1201, 1202, 1203 and so on, and White and
> > Blue will also have entries for 1201, 1202, 1203 and so on.
> > 
> > For me to get Subtotals of only Red 1201, 1202 and so on, I have to
> > filter out White and Blue so that their entries for 1201, 1202, 1203
> > are not included in the Subtotal.
> > 
> > I'm not trying to use this as a substitute for full-on accounting,
> > just to track some daily expenses very quickly and have the basic info
> > without having to wait 7 to 10 days for the accountant to give me the
> > info, which by then is far too late.  I just want to get some basic
> > subtotals very quickly, daily.
> > 
> > It sounds like I've reached a limitation in Excel, and that I'll have
> > to separate Red, White and Blue without relying on Filter to do it.
> > 
> > Maybe the best work-around is this:
> > 1.  Separate Red, White and Blue with a Sort,
> > 2.  then insert a new Header Row for each group,
> > 3.  then do a Subtotal for each group separately, one at a time
> > 
> > Outline Symbols will then appear for each group, separately, one at a
> > time.
> > 
> > Again, thanks for your replies.  I appreciate the fact that you didn't
> > just say "go to this link," or "do it with a ____(whatever the command
> > is)____."  Half the time, those links don't address the question and
> > simply waste everyone's effort, and the "whatever command" type of
> > reply usually leaves us asking WHERE that command, is and HOW we begin
> > using it.
> > 
> > Thanks,
> 
> -- 
> 
> Dave Peterson
> 
0
FrankK (2)
2/21/2009 11:42:00 PM
Reply:

Similar Artilces:

Excel XP Subtotal method not working
We have a workbook that was created in Office 2000 that contains a macro that performs subtotals on a range. We have several machines in our company that are running Office XP (various OS) and are having problems with this workbook/macro. In Office XP, the subtotal method does not work. Before adding code to the macro to stop screen updating, the spreadsheet would lock up. Now this doesn't happen but the subtotal function is not working properly. This happens on each Office XP machine. Thanks in advance. Stephanie I had the same problem with my macros when i transfer them from exce...

Subtotal in a report
I have a report I'm building to give me activity by category. Within the report I have information like Inventory on hand, inventory in transit, on order, etc (by month). I'm looking to be able to add a subtotal in the report for total inventory for each month. What's the best way to calculate this? Thanks. Please post the SQL statement you are using to "feed" that report. "How" will depend on "what"... Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in...

expand and collapse
Does anyone have any directions or examples of how to do a drill down button on a form. I want to have a topic and when the user pushes the + sign it will open forms or command buttons related to that topic and then of course I could collapse the button as well. Any suggestions would be helpful. It would work similar to this website or a pivot table. Thanks What you describe sounds like the Windows Tree View Control. There are quite a number of references in former MVP, now Microsoft employee, Jeff Conrad's "Access Junkie" website... http://www.accessmvp.com/JCo...

How do I resize shape in powerpoint (resizing collapses the shape
I can draw a shape in PowerPoint 2007 but when I click on it to change the size, it collapses vertically. I can stretch it horizontally but can't change the vertical height. Have you applied all the hot fixes and service packs to PowerPoint 2007? There were issues with this early on, but most have been resolved with the updates. Bill Dilworth "jmmarq4" <jmmarq4@discussions.microsoft.com> wrote in message news:DD51B77B-1C94-471B-A574-551191857A98@microsoft.com... >I can draw a shape in PowerPoint 2007 but when I click on it to change the > size, it...

Programmatically Expand or Collapse a sub menu
Does anyone know how to do this on a CMenu object? I'm working on a RClick feature for a CMenu. I've got to the point now where, while the menu is displayed, I can Right click on a menu item then that item will automatically be converted to a SubMenu and the little arrow on the right side appears - this all happens automatically while the menu is still displayed. Problem is that I have to move the mouse over, in order to display the new submenu. I would like for the submenu to be displayed automatically, after I've added it. Is there a hidden method on CMenu that I can't see...

How to prevent automatic collapsing of "inner" folders when collapsing "outer" folder in Outlook 2003?
In Outlook 2003 I created a big private folder tree where I store all my emails according to the topic. Assume I expanded all the folder so that the "inner" folders are all visible in the tree. Now I collapse the outermost folder and re-open (re-expand) it again immediately. I expected that the collapse/expand state of the "inner" folder is kept. Much to my surprise this is not the case. ALL folders are collapsed and I have to re-open them all manually again. This is very un-convenient. Is there an option to prevent this automatic collapsing of inner mail folders? Sven...

Subtotals or PivotTables
In the following example: For Item "a": I need subtotals of $418.38 for "CA", $67.78 for "NV" and $97.70 for "AZ" plus the total of $583.86. For Item "b": I need subtotals of $202.16 for "ME", $33.90 for "VT" and $48.84 for "OK" plus the total of $284.90. Item State Price a CA 81.08 a CA 82.70 a CA 83.68 a CA 84.62 a CA 86.30 a NV 13.54 a NV 13.56 a NV 13.56 a NV 13.56 a NV 13.56 a AZ 19.54 a AZ 19.54 a AZ 19.54 a AZ 19.54 a AZ 19.54 b ME 39.18 b ME 39.96 b ME 40.44 b ME 40.88 b ME 41.70 b VT 6.78 b VT 6.78 ...

Pivot Table Percentages for Subtotals
In a Pivot Table I am trying to get Percentages of a subtotal such that the subtotal is 100%. See Below. I would like to maintain the format. I can get to the results before the %. That is the left 3 columns. I thought I could get the percentage with "Field Settings show data as:" command. But I have been unsuccessful. Any help would be greatly appreciated Dept...................Funct...........Jan.......... 1..................A...........10...........16.7% .....................B...........20...........33.3% .....................C...........30...........50.0% 1.Total..........

Subdocuments is always collapsed when opening the masterdoc
I saved a masterdocument with all the subdocuments expanded. When I reopen the masterdoc, the subdocuments are always collapsed however. As I want to distribute this file and I am afraid that not everybody is used to working with subdocuments, I want the subdocuments to appear automatically expanded in the masterdocument. Kind regards, Robrecht As far as I know, you cannot change that behavior. More importantly, though, the master document feature in Word causes document corruption; see http://word.mvps.org/FAQs/General/WhyMasterDocsCorrupt.htm. For a safer approach, u...

Subtotal data excluding negitive numbers.
I have a spreadsheet contains lots information, each one linked to some other spreadsheet. In column =93A=94 some numbers are positive and some are negative. What I want to do is to subtotal(9,A3:A100) to show the total numbers when I filter something out. While I don=92t want these negative numbers to be calculated. I knew if I add a new column can resolve this problem, but if I the spreadsheet does not allow me to do that change, how can I put kind of formula in one single cell to show the subtotal result without adding the negative numbers? I have tried sumproduct but result only ...

Outlook 2007 collapsed folders
Its minor but annoying. For some reasons my folders (e.g. inbox, unread, etc.) have an initial collapsed view requiring me to always have to click the '+' sign. If new mail comes in I cant see the header right away until I uncollasped the folder. Did I change the default view somewhere? Thanks Kita kita@kit.com <kita@kit.com> wrote: > Its minor but annoying. For some reasons my folders (e.g. inbox, > unread, etc.) have an initial collapsed view requiring me to always > have to click the '+' sign. What tye of account? -- Brian Tillman On Thu, 9 Aug 200...

Collapsible lists
I am unfamiliar with CSS scripting or JavaScripting, so I need help in creating a collapsible list in FrontPage 2003. Can anyone give me something to copy & paste, and tell me where to insert it? Thank you! Google for: collapsible list javascript and take your pick. -- Tom [Pepper] Willett Microsoft MVP - FrontPage Since 1997 --------------------------- "Confused by FrontPage" <ConfusedbyFrontPage@discussions.microsoft.com> wrote in message news:8C3B1C65-5E12-458D-A754-09B2FD90949F@microsoft.com... :I am unfamiliar with CSS scripting or JavaScripti...

How do I subtotal data and then sort these subtotals in ascending.
Hi Steve personally i would sort the data by the category on which to subtotal then subtotal it (data / subtotals) then collapse it down so i could only see the subtotal lines select them choose edit / goto / special - visible cells only copy go to a new sheet & paste then sort that list. hope this helps Cheers JulieD "Steve_Wood" <Steve_Wood@discussions.microsoft.com> wrote in message news:456CB89E-B0D5-41A7-B646-9BC71355AA3B@microsoft.com... > Just hide the details and sort the visible cells. when you show the details, you'll be amazed (I was!) that exce...

Collapsible Panel
Hi All, I am trying to develop a collapsible panel using MFC (without ..NET). But So far no improvement. Google has lots of applications using ..NET. But how do i develop collapsible panels using pure MFC? Regards Soumya "Collapsible panel" is quite vague. What exactly are you talking about. Details please. "Daya" <soumya.dharmarajan@gmail.com> wrote in message news:1144732451.583909.211570@t31g2000cwb.googlegroups.com... > Hi All, > I am trying to develop a collapsible panel using MFC (without > .NET). But So far no improvement. Google ...

Pivot Table Question
Hi, A couple questions concerning my pivot table. Can I have subtotals in my pivot table, and if so, how do I do it? Larger fund entities are required to file 13F documents, which reveals their holdings, each calendar quarter. In my situation, I am tracking which funds own Stock ABCDE. The 13F documents are filed quarterly and it shows how many shares are owned by a) sole discretion and b) share discretion. A fund can own ABCDE shares in both the sole and share discretion categories. My pivot table has the following layout. 1) Dates (quarterly data) - Column. 2) Fund Companies - Row 3...

Subtotals #16
Is there a way to use the subtotal function on the tools menu to have a sum and avg in the same subtotal line? For instance you may want to total one column and avg another column all on the same row. Create one subtotal using that method, then copy that formula acroos, but change the 9 to a 1 -- HTH RP "JJ" <JJ@discussions.microsoft.com> wrote in message news:A6A098BC-A4F4-4CDB-AA03-159C016B9C8A@microsoft.com... > Is there a way to use the subtotal function on the tools menu to have a sum > and avg in the same subtotal line? For instance you may want to total one &g...

Collapse a report
I just finished a custom report, but I want it to default to the collapsed view. Is there a way to do that? after you generate the report, collapse it and the memorize it. the next time you generate that memorized report it will open collapsed. Craig "Rick@ASP" <RickASP@discussions.microsoft.com> wrote in message news:E20535CF-AE5E-4C60-90CE-9802FAA2FBAB@microsoft.com... >I just finished a custom report, but I want it to default to the collapsed > view. Is there a way to do that? It didn't work. The memorized one looks just like the original. ...

Date format collapses diagram
I have a strange problem which is different to explain. I have a workbook with five sheets containing data and a cell with a date in it. A sixth sheet contains a table with data collected from the first five sheets, with the dates as categories, like this; 08.2004 09.2004 10.2004 11.2004 12.2004 3 4 4 5 3 4 3 5 4 4 3 2 5 3 4 The table is used for generating a line diagram. Now, the diagram looks fine when I only include data and categories from the first four columns. If I inc...

Inbox folder automatically collapsing
I have Outlook 2007 and the Inbox folder under All Mail Items>Personal Folders>Inbox is normallly expanded at all times so I can see all the sub-folders. For some reason it started to collapse every time I delete a message in the View column or minimize my Outlook window. It just started today and I have no idea why or how to correct this without having to constantly expand the Inbox folder after every deletion, etc. Have you tried restarting outlook or rebooting? -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solu...

How do I collapse a menu in Excel
Is this question as basic as I think? Is the question asking me to minimize the Window? I have looked through all the option but do not seem to find a function to "collapse" Click anywhere other than on the menu and the menu will colapse -- HTH... Jim Thomlinson "Tiviphil" wrote: > Is this question as basic as I think? Is the question asking me to minimize > the Window? I have looked through all the option but do not seem to find a > function to "collapse" ...

subtotaling worksheets #2
very vague, I'm afraid ... any ideas -- GazM ----------------------------------------------------------------------- GazMo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1461 View this thread: http://www.excelforum.com/showthread.php?threadid=27025 I'm not sure whether I am intrepreting what you want correctly. I thin you want to create subtotals on each page and then copy the subtotals a the bottom of the page. For each worksheet you could: 1. create the subtotals - use absolute cell references, specify Jan-De columns 2. loop through the rows to f...

Collapsing Cells
I have a spreadsheet that has data I want to extract that resides in every 4th cell down. So for example: Row 1 - Name Row 2 - Address Row 3 - City State Ziop Row 4 - Phone Number It is row 4 I want to extract and I am looking for a way to extract ONLY the data in each 4th row into a spreadsheet that will have all phone numbers listed. Thanks. In A1 of Sheet2 =OFFSET(Sheet1!$A$1,4*ROW() -1,0) Drag/copy down. Gord Dibben MS Excel MVP On Mon, 26 Apr 2010 10:35:10 -0700 (PDT), Kip <hmkipp@gmail.com> wrote: >I have a spreadsheet that has data I want to...

Whitespace collapse not working on XmlValidatingReader?
Hi guys, I need an attribute (could be an element too) to have its whitespace collapsed [0]. I don't get the expected behavior, however. The schema is as follows: <xs:schema ...> <xs:element name="Root"> <xs:complexType> <xs:sequence /> <xs:attribute name="Tokenize"> <xs:simpleType> <xs:restriction base="xs:normalizedString"> <xs:whiteSpace value="collapse" /> </xs:restriction> </xs:simpleType> </xs:attribute> ...

Adding Formulas to Subtotal rows
I have a large amount of data on a spread sheet which has been sorted and subtotaled (using the “Subtotal” function on the data menu). On each subtotal row I would like to have formulas that use the results of that row’s subtotals (ie divide the value of one subtotal by the value of another subtotal on the same row). At the moment I am creating the formulas on one subtotal row and then copying them to each of the remaining subtotal rows – one row at a time. This seems to be cumbersome – is there a faster way? Thanks Hi one way: use a formula like the following =IF(ISNUMBER(FIND("Tota...

Keep folders collapsed
Why can't I set Mail just like OE was? I don't want the folders open except for the ones that contain an email that hasn't been read yet when I first start Mail? -- Thank you for your kind response, Just Me "Just Me" wrote: > Why can't I set Mail just like OE was? I don't want the folders open except > for the ones that contain an email that hasn't been read yet when I first > start Mail? > > -- > Thank you for your kind response, > Just Me ...