Use a formula in custom autofilter
 

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.

Answer: Use a formula in custom autofilter
 

1. In a cell, enter the formula =TODAY()+7.
2. Select the column of dates that you want to filter.
3. Go to the Data tab and click on the Filter button.
4. Click on the drop-down arrow in the column header and select Filter by Color Custom Filter.
5. In the Custom AutoFilter dialog box, select "is less than or equal to" from the first drop-down list.
6. In the second drop-down list, select "Cell Value" and then select the cell that contains the formula =TODAY()+7.
7. Click OK to apply the filter.

This should filter the column of dates to show only those that are less than 7 days from today. Note that the formula in the cell should not have quotes around it, and you should select "Cell Value" in the Custom AutoFilter dialog box to refer to the cell that contains the formula.

Use a formula in custom autofilter
 

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:D 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" wrote in message
...
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.

Use a formula in custom autofilter
 

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


Thanks



T. Valko wrote:
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:D 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" wrote in message
...
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.

Use a formula in custom autofilter
 

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" wrote:

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:D 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" wrote in message
...
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.

Use a formula in custom autofilter
 

Glad it helped. Thanks for the feedback!

Biff

"EP" wrote in message
news:Fxq7i.79386$n_.13971@attbi_s21...
Very Fine work Biff I was also stumped on this one.


Thanks



T. Valko wrote:
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:D 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" wrote in message
...
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.

Use a formula in custom autofilter
 

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

"andy62" wrote in message
...
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" wrote:

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:D 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" wrote in message
...
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.