I am trying to filter a list that has a column of start dates, and a column
of end dates. I want to ONLY show the end dates which are still in the
future. Essentially - like a subscription list - who still has a valid
subscription based on the end date of when their subscription ended. I can
do an If/Then finction in another sheet, but I can only get that to go line
by line to compare, and leave a blank if not true. How can I do this, either
with Filter or if/then to only see current subscriptions in a different sheet
than the main database? Any Help wouldbe great! :)

Here's a simple formulas set-up to accomplish it ..

Assume source data in Sheet1, cols A to E,
data from row2 down, key col = col C (end-dates)

In another sheet
In B2: =IF(Sheet1!C2TODAY(),ROW(),"")
Leave B1 empty. This is your criteria col.

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(Sheet1!A:A,SM ALL($B:$B,ROWS($1:1))))
Copy C2 across by 5 cols to G2. Select B2:G2, fill down to cover the max
expected extent of source data in Sheet1, say down to G100. Minimize/hide
away col B. Cols C to G will return the required results from Sheet1, with
all lines neatly packed at the top.

Success? Punch it here, click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Zebrageek" wrote:
I am trying to filter a list that has a column of start dates, and a column
of end dates. I want to ONLY show the end dates which are still in the
future. Essentially - like a subscription list - who still has a valid
subscription based on the end date of when their subscription ended. I can
do an If/Then finction in another sheet, but I can only get that to go line
by line to compare, and leave a blank if not true. How can I do this, either
with Filter or if/then to only see current subscriptions in a different sheet
than the main database? Any Help would be great! :)

The Auto Filter has a "custom" option for each column that allows you
to specify "is greater than". Just click the filter arrow in the End Date
column and select custom.
--
Jim Cone
Portland, Oregon USA



"Zebrageek"
wrote in message
I am trying to filter a list that has a column of start dates, and a column
of end dates. I want to ONLY show the end dates which are still in the
future. Essentially - like a subscription list - who still has a valid
subscription based on the end date of when their subscription ended. I can
do an If/Then finction in another sheet, but I can only get that to go line
by line to compare, and leave a blank if not true. How can I do this, either
with Filter or if/then to only see current subscriptions in a different sheet
than the main database? Any Help wouldbe great! :)

Worked perfectly! Thank you! I was going nuts trying to figure out that
second part! :) I also already tried the custom filter, but I couldn't get
that to automatically update the data daily wihtout me having to manually
tell it what date it was. Thanks again for the help.

"Max" wrote:

Here's a simple formulas set-up to accomplish it ..

Assume source data in Sheet1, cols A to E,
data from row2 down, key col = col C (end-dates)

In another sheet
In B2: =IF(Sheet1!C2TODAY(),ROW(),"")
Leave B1 empty. This is your criteria col.

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(Sheet1!A:A,SM ALL($B:$B,ROWS($1:1))))
Copy C2 across by 5 cols to G2. Select B2:G2, fill down to cover the max
expected extent of source data in Sheet1, say down to G100. Minimize/hide
away col B. Cols C to G will return the required results from Sheet1, with
all lines neatly packed at the top.

Success? Punch it here, click YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Zebrageek" wrote:
I am trying to filter a list that has a column of start dates, and a column
of end dates. I want to ONLY show the end dates which are still in the
future. Essentially - like a subscription list - who still has a valid
subscription based on the end date of when their subscription ended. I can
do an If/Then finction in another sheet, but I can only get that to go line
by line to compare, and leave a blank if not true. How can I do this, either
with Filter or if/then to only see current subscriptions in a different sheet
than the main database? Any Help would be great! :)

Welcome, glad to hear.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Zebrageek" wrote in message
...
Worked perfectly! Thank you! I was going nuts trying to figure out that
second part! :) I also already tried the custom filter, but I couldn't get
that to automatically update the data daily wihtout me having to manually
tell it what date it was. Thanks again for the help.