Auto filter by month

[stuart noble]

Auto filter does pretty much everything I need apart from filtering by
month. Col A contains dd/mm/yy type dates, and at the moment I have the date
repeated in a mmm format in col B i.e. if(a1,a1,""), so the only purpose of
B is the auto filter. This is fine except I have to remember how many cells
I've entered the formula into in column B. Is there a way to generate it
automatically when data is entered in A?
Any help appreciated or, if I'm going about this the wrong way, alternative
suggestions much appreciated.

 

[JMay]

In most similar situations in Column B I would use a formula

=YEAR(A10)&"-"&TEXT(MONTH(A10),"00")

HTH

 

[Arvi Laanemets]

Hi

=IF(A1="","",A1)

and to avoid resetting the autofilter every time you add some row(s), select
the would-be datarange before setting autofilter on.


Arvi Laanemets

 

[Andy Brown]

Stuart,

You could use event code that "does stuff" in column B whenever column A (or
a given range within same) is updated. However, I'm guessing you're not
aware of AutoFilter's Custom option, which you can use to show rows where
column A is greater than or equal to 01/01/2003 *and* less than 01/02/2003
(examples in format dd/mm/yyyy).

Rgds,
Andy

 

[Debra Dalgleish]

If you use Data>Form to enter new records, the formulas will be added
automatically. Or, you can use John Walkenbach's Enhanced Data Form (a
free add-in):

http://j-walk.com/ss/dataform/index.htm

It allows comboboxes, and unlimited fields.

 

[stuart noble]

If you use Data>Form to enter new records, the formulas will be added
automatically. Or, you can use John Walkenbach's Enhanced Data Form (a
free add-in):

http://j-walk.com/ss/dataform/index.htm

It allows comboboxes, and unlimited fields.

Cheers Debra. Much appreciated.

 

[stuart noble]

stuart noble wrote in message ...

Cheers Debra. Much appreciated.

At first glance it seems that neither of these can accommodate data
validation from a list, which is crucial in this instance.