Create a dynamic list

[SteveT]

I have a table with columns for Expiration Date, Address, , Certificate
Number, and various other data. I wish to create a list on another tab based
on criteria. For example, I would like a list of all the Certificates that
expire in the next 30 days, 31-60 days, expired, good... I have a column
calculation the time to expiration. Can someone please tell me how to
proceed from here.

Thanks
steve

Status Exp Date CO Number
Expired 3/23/2007 948
Expired 5/28/2007 949
Good 3/27/2108 950
Expired 4/28/2007 951
Expired 7/25/2007 952
Expired 7/27/2007 953
Expired 5/30/2007 954
30 Days 12/30/2008 955

 

[Shane Devenshire]

Hi,

Your title says one thing but the body of your post says another. You can
apply the AutoFilter or the Advanced Filter to find all the items that expire
in any given time period. If you want want the time to expiration a formula
like =DateColumn-TODAY() will do it, where DateColumn is the column with the
expiration date.

You can automate this with VBA. - Record the macro. In 2007 set the
criteria and just refresh the filter. You gave us no info on the version of
Excel you are using.

This can also be done with formulas but they are rather more complicated
than AutoFilter, the results of which can be copied to another sheet or range.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Max]

Here's one way to model it up to deliver the desired automated aging-criteria
results into separate sheets, using relatively simple non-array formulas

Illustrated in this sample:
http://freefilehosting.net/download/42fem
Dynamic Aged Lists by Expiry Dates.xls

Assume source table in sheet: x, cols A to C, data from row2 down, where the
key col = col B (Exp Date) which is presumed to contain real dates

Use 4 adjacent cols to the right - cols E to H
Put labels into E1:H1: Expired, <=30, 31-<=60, >60
In E2: =IF($B2="","",IF($B2<=TODAY(),ROW(),""))
In F2: =IF($B2="","",IF(AND($B2>TODAY(),$B2<=TODAY()+30),ROW(),""))
In G2: =IF($B2="","",IF(AND($B2>TODAY()+30,$B2<=TODAY()+60),ROW(),""))
In H2: =IF($B2="","",IF($B2>TODAY()+60,ROW(),""))
Copy E2:H2 down to cover the max expected extent of data in col B. This sets
up the criteria range

In a new sheet: Expired,
Put in A2: =IF(B2="","",ROWS($1:1))
Put in B2:
=IF(ROWS($1:1)>COUNT(x!$E:$E),"",INDEX(x!A:A,SMALL(x!$E:$E,ROWS($1:1))))
Copy B2 to D2. Select A22, copy down to cover the same max expected extent
of source data. Format col C as date. Cols B to D will return only the
"Expired" lines from x, all neatly packed at the top. Col A provides a
running serial numbering for the returns

Make a copy of the sheet, name it as: <=30
Replace B2 with this:
=IF(ROWS($1:1)>COUNT(x!$F:$F),"",INDEX(x!A:A,SMALL(x!$F:$F,ROWS($1:1))))
Essentially the same formula, except adjusted to point to col F in x (col F
is the criteria col for "<=30" cases). Copy B2 to D2, fill down, to return
the "<=30" cases.

Repeat likewise to extract the remaining 2 aging categories: 31-<=60 and >60
in 2 other sheets. (Change it to point to cols G and H in x)

When completed you'd have it fullly dynamic, with lines in x automatically
aged & copied to either one of the 4 aging sheets: Expired, <=30, 31-<=60, >60
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[Max]

Take away this sample instead,
some documentation discrepancies corrected:

http://freefilehosting.net/download/42fff
Dynamic Aged Lists by Expiry Dates.xls
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---