column Filter question

Have a worksheet with several columns. Region, District, State, Name,
etc,etc, etc. I have displayed the sub-totals in cell (=subtotal(2,A;A) but
want to include the column name (i.e Region 1234, State 123). Is there a
formula that can include the column name. Or even in a adjoining cell..
Thanks very much.

bg

Hi Bg,

try to include a name ( INSERT | NAME ) for the column and use it on the
formula, eg. select all data from column "Region" and named it as "Region"

hth
regards from Brazil
Marcelo

"Bg" escreveu:

> Have a worksheet with several columns. Region, District, State, Name,
> etc,etc, etc. I have displayed the sub-totals in cell (=subtotal(2,A;A) but
> want to include the column name (i.e Region 1234, State 123). Is there a
> formula that can include the column name. Or even in a adjoining cell..
> Thanks very much.
>
> bg
>
>
>

Marcelo,
Thanks for the response, but I don't understand what your suggesting. Where
should I add ( INSERT | NAME ) to my existing formula?
=subtotal(2,C:C)

Thanks
Bg

"Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
news:CEAAD6B3-0432-4F86-9E9C-69BC823AFF44@microsoft.com...
> Hi Bg,
>
> try to include a name ( INSERT | NAME ) for the column and use it on the
> formula, eg. select all data from column "Region" and named it as "Region"
>
> hth
> regards from Brazil
> Marcelo
>
> "Bg" escreveu:
>
>> Have a worksheet with several columns. Region, District, State, Name,
>> etc,etc, etc. I have displayed the sub-totals in cell (=subtotal(2,A;A)
>> but
>> want to include the column name (i.e Region 1234, State 123). Is there a
>> formula that can include the column name. Or even in a adjoining cell..
>> Thanks very much.
>>
>> bg
>>
>>
>>

Hi Bg, maybe I did not understand exactly you are looking for.

you told about the Column name what it's mean? A, B or C?

regards

"Bg" escreveu:

> Marcelo,
> Thanks for the response, but I don't understand what your suggesting. Where
> should I add ( INSERT | NAME ) to my existing formula?
> =subtotal(2,C:C)
>
> Thanks
> Bg
>
> "Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
> news:CEAAD6B3-0432-4F86-9E9C-69BC823AFF44@microsoft.com...
> > Hi Bg,
> >
> > try to include a name ( INSERT | NAME ) for the column and use it on the
> > formula, eg. select all data from column "Region" and named it as "Region"
> >
> > hth
> > regards from Brazil
> > Marcelo
> >
> > "Bg" escreveu:
> >
> >> Have a worksheet with several columns. Region, District, State, Name,
> >> etc,etc, etc. I have displayed the sub-totals in cell (=subtotal(2,A;A)
> >> but
> >> want to include the column name (i.e Region 1234, State 123). Is there a
> >> formula that can include the column name. Or even in a adjoining cell..
> >> Thanks very much.
> >>
> >> bg
> >>
> >>
> >>
>
>
>

Sorry,
I have column "A" with a cell named District, column "B" with a cell named
Region, etc. Have auto-filters applied and want to have the subtotals show
values of the filtered columns and show the "filtered" numbers with the cell
reference. (i.e. Region 234)
A B C D E F

District Region State Name Area Numbers


Thanks again,
bg
"Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
news:2C32899D-540F-4151-BE13-CE3E471EE62C@microsoft.com...
> Hi Bg, maybe I did not understand exactly you are looking for.
>
> you told about the Column name what it's mean? A, B or C?
>
> regards
>
> "Bg" escreveu:
>
>> Marcelo,
>> Thanks for the response, but I don't understand what your suggesting.
>> Where
>> should I add ( INSERT | NAME ) to my existing formula?
>> =subtotal(2,C:C)
>>
>> Thanks
>> Bg
>>
>> "Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
>> news:CEAAD6B3-0432-4F86-9E9C-69BC823AFF44@microsoft.com...
>> > Hi Bg,
>> >
>> > try to include a name ( INSERT | NAME ) for the column and use it on
>> > the
>> > formula, eg. select all data from column "Region" and named it as
>> > "Region"
>> >
>> > hth
>> > regards from Brazil
>> > Marcelo
>> >
>> > "Bg" escreveu:
>> >
>> >> Have a worksheet with several columns. Region, District, State, Name,
>> >> etc,etc, etc. I have displayed the sub-totals in cell
>> >> (=subtotal(2,A;A)
>> >> but
>> >> want to include the column name (i.e Region 1234, State 123). Is there
>> >> a
>> >> formula that can include the column name. Or even in a adjoining
>> >> cell..
>> >> Thanks very much.
>> >>
>> >> bg
>> >>
>> >>
>> >>
>>
>>
>>

Bg, the contens "Regions" or are named Regions? I'm asking because you could
gave a name for a cell or a range (insert | name) as I told you before... if
you just want the "header" of the column before the result of the subtotal to
identify for where the results came use

="Region"&" "&subtotal(2,b:b)

hth
regards from Brazil.



"Bg" escreveu:

> Sorry,
> I have column "A" with a cell named District, column "B" with a cell named
> Region, etc. Have auto-filters applied and want to have the subtotals show
> values of the filtered columns and show the "filtered" numbers with the cell
> reference. (i.e. Region 234)
> A B C D E F
>
> District Region State Name Area Numbers
>
>
> Thanks again,
> bg
> "Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
> news:2C32899D-540F-4151-BE13-CE3E471EE62C@microsoft.com...
> > Hi Bg, maybe I did not understand exactly you are looking for.
> >
> > you told about the Column name what it's mean? A, B or C?
> >
> > regards
> >
> > "Bg" escreveu:
> >
> >> Marcelo,
> >> Thanks for the response, but I don't understand what your suggesting.
> >> Where
> >> should I add ( INSERT | NAME ) to my existing formula?
> >> =subtotal(2,C:C)
> >>
> >> Thanks
> >> Bg
> >>
> >> "Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
> >> news:CEAAD6B3-0432-4F86-9E9C-69BC823AFF44@microsoft.com...
> >> > Hi Bg,
> >> >
> >> > try to include a name ( INSERT | NAME ) for the column and use it on
> >> > the
> >> > formula, eg. select all data from column "Region" and named it as
> >> > "Region"
> >> >
> >> > hth
> >> > regards from Brazil
> >> > Marcelo
> >> >
> >> > "Bg" escreveu:
> >> >
> >> >> Have a worksheet with several columns. Region, District, State, Name,
> >> >> etc,etc, etc. I have displayed the sub-totals in cell
> >> >> (=subtotal(2,A;A)
> >> >> but
> >> >> want to include the column name (i.e Region 1234, State 123). Is there
> >> >> a
> >> >> formula that can include the column name. Or even in a adjoining
> >> >> cell..
> >> >> Thanks very much.
> >> >>
> >> >> bg
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

To show the name of the region that has been selected in the AutoFilter
dropdown, you can create a User Defined Function. Tom Ogilvy posted the
following function, that returns the criteria from a column in an
autofiltered table. It will show both criteria if there are two, and
includes the operator.

David McRitchie has instructions for storing a macro:
http://www.mvps.org/dmcritchie/excel....htm#havemacro


'===============================================
Public Function ShowFilter(rng As Range)
'UDF that displays the filter criteria.
'posted by Tom Ogilvy 1/17/02
'To make it respond to a filter change, tie it to the subtotal command.
'=showfilter(B2)&CHAR(SUBTOTAL(9,B3)*0+32)
'So the above would show the criteria for column B


Dim filt As Filter
Dim sCrit1 As String
Dim sCrit2 As String
Dim sop As String
Dim lngOp As Long
Dim lngOff As Long
Dim frng As Range
Dim sh As Worksheet
Set sh = rng.Parent
If sh.FilterMode = False Then
ShowFilter = "No Active Filter"
Exit Function
End If
Set frng = sh.AutoFilter.Range


If Intersect(rng.EntireColumn, frng) Is Nothing Then
ShowFilter = CVErr(xlErrRef)
Else
lngOff = rng.Column - frng.Columns(1).Column + 1
If Not sh.AutoFilter.Filters(lngOff).On Then
ShowFilter = "No Conditions"
Else
Set filt = sh.AutoFilter.Filters(lngOff)
On Error Resume Next
sCrit1 = filt.Criteria1
sCrit2 = filt.Criteria2
lngOp = filt.Operator
If lngOp = xlAnd Then
sop = " And "
ElseIf lngOp = xlOr Then
sop = " or "
Else
sop = ""
End If
ShowFilter = sCrit1 & sop & sCrit2
End If
End If
End Function
'==============================================



Bg wrote:
> Sorry,
> I have column "A" with a cell named District, column "B" with a cell named
> Region, etc. Have auto-filters applied and want to have the subtotals show
> values of the filtered columns and show the "filtered" numbers with the cell
> reference. (i.e. Region 234)
> A B C D E F
>
> District Region State Name Area Numbers
>
>
> Thanks again,
> bg
> "Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
> news:2C32899D-540F-4151-BE13-CE3E471EE62C@microsoft.com...
>
>>Hi Bg, maybe I did not understand exactly you are looking for.
>>
>>you told about the Column name what it's mean? A, B or C?
>>
>>regards
>>
>>"Bg" escreveu:
>>
>>
>>>Marcelo,
>>>Thanks for the response, but I don't understand what your suggesting.
>>>Where
>>>should I add ( INSERT | NAME ) to my existing formula?
>>>=subtotal(2,C:C)
>>>
>>>Thanks
>>>Bg
>>>
>>>"Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
>>>news:CEAAD6B3-0432-4F86-9E9C-69BC823AFF44@microsoft.com...
>>>
>>>>Hi Bg,
>>>>
>>>>try to include a name ( INSERT | NAME ) for the column and use it on
>>>>the
>>>>formula, eg. select all data from column "Region" and named it as
>>>>"Region"
>>>>
>>>>hth
>>>>regards from Brazil
>>>>Marcelo
>>>>
>>>>"Bg" escreveu:
>>>>
>>>>
>>>>>Have a worksheet with several columns. Region, District, State, Name,
>>>>>etc,etc, etc. I have displayed the sub-totals in cell
>>>>>(=subtotal(2,A;A)
>>>>>but
>>>>>want to include the column name (i.e Region 1234, State 123). Is there
>>>>>a
>>>>>formula that can include the column name. Or even in a adjoining
>>>>>cell..
>>>>>Thanks very much.
>>>>>
>>>>>bg
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>>
>
>


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

M,
Sorry, I'm not explaining this right. I understand how to add "text" to the
formula. Question is, how to add the name on the column that is filtered and
add it to the subtotal count. Any one of the columns might be filtered,
therefore the text might change from "Regions" or "District" or "Name", etc.

Bg

"Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
news:E2FBFFDB-2252-4B7D-8599-FE47DB261F0D@microsoft.com...
> Bg, the contens "Regions" or are named Regions? I'm asking because you
> could
> gave a name for a cell or a range (insert | name) as I told you before...
> if
> you just want the "header" of the column before the result of the subtotal
> to
> identify for where the results came use
>
> ="Region"&" "&subtotal(2,b:b)
>
> hth
> regards from Brazil.
>
>
>
> "Bg" escreveu:
>
>> Sorry,
>> I have column "A" with a cell named District, column "B" with a cell
>> named
>> Region, etc. Have auto-filters applied and want to have the subtotals
>> show
>> values of the filtered columns and show the "filtered" numbers with the
>> cell
>> reference. (i.e. Region 234)
>> A B C D E F
>>
>> District Region State Name Area Numbers
>>
>>
>> Thanks again,
>> bg
>> "Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
>> news:2C32899D-540F-4151-BE13-CE3E471EE62C@microsoft.com...
>> > Hi Bg, maybe I did not understand exactly you are looking for.
>> >
>> > you told about the Column name what it's mean? A, B or C?
>> >
>> > regards
>> >
>> > "Bg" escreveu:
>> >
>> >> Marcelo,
>> >> Thanks for the response, but I don't understand what your suggesting.
>> >> Where
>> >> should I add ( INSERT | NAME ) to my existing formula?
>> >> =subtotal(2,C:C)
>> >>
>> >> Thanks
>> >> Bg
>> >>
>> >> "Marcelo" <Marcelo@discussions.microsoft.com> wrote in message
>> >> news:CEAAD6B3-0432-4F86-9E9C-69BC823AFF44@microsoft.com...
>> >> > Hi Bg,
>> >> >
>> >> > try to include a name ( INSERT | NAME ) for the column and use it on
>> >> > the
>> >> > formula, eg. select all data from column "Region" and named it as
>> >> > "Region"
>> >> >
>> >> > hth
>> >> > regards from Brazil
>> >> > Marcelo
>> >> >
>> >> > "Bg" escreveu:
>> >> >
>> >> >> Have a worksheet with several columns. Region, District, State,
>> >> >> Name,
>> >> >> etc,etc, etc. I have displayed the sub-totals in cell
>> >> >> (=subtotal(2,A;A)
>> >> >> but
>> >> >> want to include the column name (i.e Region 1234, State 123). Is
>> >> >> there
>> >> >> a
>> >> >> formula that can include the column name. Or even in a adjoining
>> >> >> cell..
>> >> >> Thanks very much.
>> >> >>
>> >> >> bg
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>