Filter

[Khalil Handal]

Hi to all
Range B7:B800 has month names: Jan, Feb, .... until Dec.
I use filter to select certain months.
What formula do I need in cell L5 to show the month being filltered.
i.e. if I filltered for Mar then in cell L5, I want to see the value of
"March 2008" (without the quotes.)

 

[Khalil Handal]

Your right about the years. The cells in the range B7:B800 have the format
of: =TEXT(B7,"MMM") and they are dajcent cells. I should have mentioned that
all the dates are only for ONE year (2008).
To clarify:
When I apply the filter for March, I will see only the line that has the
value March. I want to have that value also in cell L5.
In other Words: I want the filtered value to be in cell L5.
I hope that it is more clear.

Khalil
You question is not clear to me. If you only have month names in B7:b800,
there is no way for Excel to know to which year they belong. If you do
have date values, you can either enter a Custom format:
[Format] [Cells] /Custom /Type: mmmm yyyy

To do this in an adjacent cell use the Text function:
=TEXT(B7,"mmmm yyyy")

Note: This will result in a badly sorted filter list in this column. An
alternate method to overcome this is to format with the year first::

=TEXT(B7,"yyyy mmmm")

HTH
--
Steve

Hi to all
Range B7:B800 has month names: Jan, Feb, .... until Dec.
I use filter to select certain months.
What formula do I need in cell L5 to show the month being filltered.
i.e. if I filltered for Mar then in cell L5, I want to see the value of
"March 2008" (without the quotes.)

 

[Debra Dalgleish]

There are a couple of sample files here that might help you:

http://www.contextures.com/excelfiles.html

Under filters, look for FL0020 - Fast Filter, by Roger Govier. With this
example, you could type your criteria in the row above the filtered range.

Also under Filters, look for FL0018 - Highlight Filtered Headings in
List. This shows the selected criteria in columns to the right of the
filtered range. You could modify that code to show only the month selected.

 

[AltaEgo]

Khalil
You question is not clear to me. If you only have month names in B7:b800,
there is no way for Excel to know to which year they belong. If you do
have date values, you can either enter a Custom format:
[Format] [Cells] /Custom /Type: mmmm yyyy

To do this in an adjacent cell use the Text function:
=TEXT(B7,"mmmm yyyy")

Note: This will result in a badly sorted filter list in this column. An
alternate method to overcome this is to format with the year first::

=TEXT(B7,"yyyy mmmm")

HTH

 

[Khalil Handal]

Thanks to you and Debral. It made it mored understandable.

I still don't clearly understand your needs. It is not clear whether the
values in B7:B800 are dates with the format change or contain a formula
that changes the display of a value in another column.

In response to your reply, the Text() function is NOT a format. A format
changes the way you see a result. The text function changes a value to
text. If you used the Text() function, you can filter on the result. In
fact, if you simply format you date column in the custom format below,
Excel is smart enough to filter data that displays the same despite the
underlying values not changing.

Before you respond, you may find it easier to do some background reading
on date/times in Excel. Chip Pearson covers it well:

http://www.cpearson.com/excel/datetime.htm

Google found the following video that shows how to apply a custom format:

http://mistupid.com/viewlets/excel/xldatecustom.htm

Note: the video shows how to apply 'ddd' and 'dddd'. You can use mmm or
mmmm for month and yyyy for year.

Good luck.

--
Steve

Your right about the years. The cells in the range B7:B800 have the
format of: =TEXT(B7,"MMM") and they are dajcent cells. I should have
mentioned that all the dates are only for ONE year (2008).
To clarify:
When I apply the filter for March, I will see only the line that has the
value March. I want to have that value also in cell L5.
In other Words: I want the filtered value to be in cell L5.
I hope that it is more clear.

Khalil
You question is not clear to me. If you only have month names in
B7:b800, there is no way for Excel to know to which year they belong. If
you do
have date values, you can either enter a Custom format:
[Format] [Cells] /Custom /Type: mmmm yyyy

To do this in an adjacent cell use the Text function:
=TEXT(B7,"mmmm yyyy")

Note: This will result in a badly sorted filter list in this column. An
alternate method to overcome this is to format with the year first::

=TEXT(B7,"yyyy mmmm")

HTH
--
Steve

Hi to all
Range B7:B800 has month names: Jan, Feb, .... until Dec.
I use filter to select certain months.
What formula do I need in cell L5 to show the month being filltered.
i.e. if I filltered for Mar then in cell L5, I want to see the value
of "March 2008" (without the quotes.)

 

[AltaEgo]

I still don't clearly understand your needs. It is not clear whether the
values in B7:B800 are dates with the format change or contain a formula that
changes the display of a value in another column.

In response to your reply, the Text() function is NOT a format. A format
changes the way you see a result. The text function changes a value to text.
If you used the Text() function, you can filter on the result. In fact, if
you simply format you date column in the custom format below, Excel is smart
enough to filter data that displays the same despite the underlying values
not changing.

Before you respond, you may find it easier to do some background reading on
date/times in Excel. Chip Pearson covers it well:

http://www.cpearson.com/excel/datetime.htm

Google found the following video that shows how to apply a custom format:

http://mistupid.com/viewlets/excel/xldatecustom.htm

Note: the video shows how to apply 'ddd' and 'dddd'. You can use mmm or
mmmm for month and yyyy for year.

Good luck.

--
Steve

Your right about the years. The cells in the range B7:B800 have the format
of: =TEXT(B7,"MMM") and they are dajcent cells. I should have mentioned
that all the dates are only for ONE year (2008).
To clarify:
When I apply the filter for March, I will see only the line that has the
value March. I want to have that value also in cell L5.
In other Words: I want the filtered value to be in cell L5.
I hope that it is more clear.

Khalil
You question is not clear to me. If you only have month names in B7:b800,
there is no way for Excel to know to which year they belong. If you do
have date values, you can either enter a Custom format:
[Format] [Cells] /Custom /Type: mmmm yyyy

To do this in an adjacent cell use the Text function:
=TEXT(B7,"mmmm yyyy")

Note: This will result in a badly sorted filter list in this column. An
alternate method to overcome this is to format with the year first::

=TEXT(B7,"yyyy mmmm")

HTH
--
Steve

Hi to all
Range B7:B800 has month names: Jan, Feb, .... until Dec.
I use filter to select certain months.
What formula do I need in cell L5 to show the month being filltered.
i.e. if I filltered for Mar then in cell L5, I want to see the value of
"March 2008" (without the quotes.)