![]() |
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
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. |
All times are GMT +1. The time now is 05:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com