SUBTOTAL and FILTER

Hi,

I have a spreadsheet created by someone else. Autofilter is on all columns,
and there is a row at the bottom called TOTAL which has SUBTOTAL values in
it. When I filter, the TOTAL row is always displayed, which is what I want,
but I cant really understand why it is always displayed.
I actually want to add a second row which will always be displayed, but
beacuse I dont understand how it works, i'm not sure how to do this.

any ideas would be helpful

thanks!

It sounds like there is a macro coming in to play which re-places that
formula each time a new "filter" is effected. One way to do this without
macros, which I use a lot is to FREEZE the top 8 or 10 rows on the sheet and
put my SUBTOTAL formulas above that freezeline, that way they are always
visible no matter where I am looking at the filtered data.

hth
Vaya con Dios,
Chuck, CABGx3



"confused" wrote:

> Hi,
>
> I have a spreadsheet created by someone else. Autofilter is on all columns,
> and there is a row at the bottom called TOTAL which has SUBTOTAL values in
> it. When I filter, the TOTAL row is always displayed, which is what I want,
> but I cant really understand why it is always displayed.
> I actually want to add a second row which will always be displayed, but
> beacuse I dont understand how it works, i'm not sure how to do this.
>
> any ideas would be helpful
>
> thanks!
>

hi, thanks for your help but there is no macro!
the problem is that the total row calculates the total of all rows and if
you filter to show only some rows, the total row gives a new total. this is
the effect i want to see and freeze panes will not allow me to do this,

can anyone advise?

thanks


"CLR" wrote:

> It sounds like there is a macro coming in to play which re-places that
> formula each time a new "filter" is effected. One way to do this without
> macros, which I use a lot is to FREEZE the top 8 or 10 rows on the sheet and
> put my SUBTOTAL formulas above that freezeline, that way they are always
> visible no matter where I am looking at the filtered data.
>
> hth
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "confused" wrote:
>
> > Hi,
> >
> > I have a spreadsheet created by someone else. Autofilter is on all columns,
> > and there is a row at the bottom called TOTAL which has SUBTOTAL values in
> > it. When I filter, the TOTAL row is always displayed, which is what I want,
> > but I cant really understand why it is always displayed.
> > I actually want to add a second row which will always be displayed, but
> > beacuse I dont understand how it works, i'm not sure how to do this.
> >
> > any ideas would be helpful
> >
> > thanks!
> >

If you have a Subtotal formula in the last row of the table, that row is
excluded from the filter, and always remains visible.

If you want to add rows after the subtotal row, and have all these
summary rows remain visible, add a blank row between the table and the
summary rows.

confused wrote:
> Hi,
>
> I have a spreadsheet created by someone else. Autofilter is on all columns,
> and there is a row at the bottom called TOTAL which has SUBTOTAL values in
> it. When I filter, the TOTAL row is always displayed, which is what I want,
> but I cant really understand why it is always displayed.
> I actually want to add a second row which will always be displayed, but
> beacuse I dont understand how it works, i'm not sure how to do this.
>
> any ideas would be helpful
>
> thanks!
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Confused - hopefully not for long!

With filters, the visible lines can be above the dropdown arrows of the filters or below the selection that was filtered.
I'm assuming that your subtotals are at the bottom of your data, in which case getting the subtotals to always show, is done by selecting the range before applying the autofilter (nb actual filter, not just filter criteria).

To always show another row you may need to (shortcuts may work depending on version of Excel):
*Remove the autofilter eg [alt + d + f + f],
(My understanding is that if you insert the row while there is an existing autofilter the new row would be incorporated into the filter range therefore I remove it first.)
*Insert a row between your subtotal & data eg [alt + i + r],
*Select the range to filter excluding the subtotal row & the new row above it (depending on layout maybe able to use, [ctrl + home], [ctrl + shift + end], [shift + up arrow + up arrow])
*Replace autofilter eg [alt + d + f + f], and
* you should be away laughing!

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

If you put a SUBTOTAL formula above the FreezeLine, it will indeed calculate
only the filtered items in the specified column, and will remain visible
throughout the scroll of the data.

Vaya con Dios,
Chuck, CABGx3



"confused" wrote:

> hi, thanks for your help but there is no macro!
> the problem is that the total row calculates the total of all rows and if
> you filter to show only some rows, the total row gives a new total. this is
> the effect i want to see and freeze panes will not allow me to do this,
>
> can anyone advise?
>
> thanks
>
>
> "CLR" wrote:
>
> > It sounds like there is a macro coming in to play which re-places that
> > formula each time a new "filter" is effected. One way to do this without
> > macros, which I use a lot is to FREEZE the top 8 or 10 rows on the sheet and
> > put my SUBTOTAL formulas above that freezeline, that way they are always
> > visible no matter where I am looking at the filtered data.
> >
> > hth
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "confused" wrote:
> >
> > > Hi,
> > >
> > > I have a spreadsheet created by someone else. Autofilter is on all columns,
> > > and there is a row at the bottom called TOTAL which has SUBTOTAL values in
> > > it. When I filter, the TOTAL row is always displayed, which is what I want,
> > > but I cant really understand why it is always displayed.
> > > I actually want to add a second row which will always be displayed, but
> > > beacuse I dont understand how it works, i'm not sure how to do this.
> > >
> > > any ideas would be helpful
> > >
> > > thanks!
> > >