Use a formula in custom autofilter

[andy62]

I want to autofilter a column of dates to show only those that are less than
7 days from today. In the Autofilter >> Custom feature I've tried
"=TODAY()+7" (with and without quotes) but that does not seem to work. I
know I could just put in the exact date but I was trying to write a simple
macro, so I wanted the date to be a variable. Does Autofilter not take a
formula? Then I tried putting "=TODAY()+7" in a cell and referring the
custom autofilter to it, put that didn't work either. Any ideas?

TIA.

 

[T. Valko]

Autofilter won't accept formulas in the dialogs. You can however use a
formula in a helper column and then filter on the result of that formula.

Assume column A are the dates. Columns B are other data.

In column E enter a formula like this and copy down to the end of the dates
in column A:

=A2<TODAY()+7

This will return either TRUE or FALSE. Now, filter on column E being TRUE

You can also use the Advanced filter where, instead of using a whole column
of helper formulas you use just a single cell with a formula.

Biff

 

[andy62]

Thanks, Biff. I developed a little workaround that I'm happy with. I
created a single reference cell with the formula "=TODAY()+7". The rest is
coded into a macro:
1) select the reference cell and press Ctrl-C to Copy
2) Press the autofilter arrow and select "Custom"
3) Select "Is less than" in the first field
4) Select the second field
5) Press Ctrl-V to Paste
6) Press "Okay"

I was surprised that the copied cell remained active during steps 2-4, and
that the results of the cell (the date 7 days from today) showed up after
Pasting.

Thanks again.

 

[T. Valko]

Glad it helped. Thanks for the feedback!

Biff

Very Fine work Biff I was also stumped on this one.


Thanks

 

[T. Valko]

If you found a way to make it work that's great but it sounds like you
taking the long way around to get where you want.

You should post this in the Programming group. I'm sure this could be coded
without having to go through those steps.

Biff