# Creating a list dependant on values

#### Yuee

##### New Member
I am trying to create a database for a fitness centre that will create a list of people whose membership has expired or is about to expire.

I have created a formula so that it will subtract their membership end date from the current date, which will give the number of days remaining in their membership. Thus everyone with a negative number has already expired.

My question is how do I get it to search through the whole database and create a list of everyone who has expired (negative number) on a new worksheet?

I imagine this may be a relatively easy formula, but it's got me stumped.

If you could help that would be greatly appreciated.

Cheers,
Yuee

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Welcome to the board!

E1:E2 is the criteria range.
Book1
ABCDEFGHIJ
1Nameend dateexpexpNameend dateexp
2MR12005-11-20-5<0MR12005-11-20-5
3MR22005-11-21-4MR22005-11-21-4
4MR32005-11-22-3MR32005-11-22-3
5MR42005-11-23-2MR42005-11-23-2
6MR52005-11-24-1MR52005-11-24-1
7MR62005-11-250
8MR72005-11-261
9MR82005-11-272
10MR92005-11-283
11MR102005-11-294
12MR112005-11-305
13MR122005-12-016
14MR132005-12-027
15MR142005-12-038
16MR152005-12-049
17
Sheet1

Welcome to MrExcel!

You could also use AUTOFILTER, filter the list for expired dates, and then print the list. Have you used AUTOFILTER?

Thanks for the warm welcome...but most of all for the help.

I have used auto filter before, but not the advanced filter. This pretty much solves my problem.

Thanks again.

Cheers,
Yuee

Sorry, 1 more question.

With the advanced filter how would i write the criteria if I want it to list out the people that will expire within the next 31 days...ie. How do I get it look for everyone with >0 days left and everyone with <31 days?

Change criteria range to:
Book1
ABCDEFGHIJ
1Nameend dateexpexpexpNameend dateexp
2MR12005-11-20-6>0<31MR82005-11-271
3MR22005-11-21-5MR92005-11-282
4MR32005-11-22-4MR102005-11-293
5MR42005-11-23-3MR112005-11-304
6MR52005-11-24-2MR122005-12-015
7MR62005-11-25-1MR132005-12-026
8MR72005-11-260MR142005-12-037
9MR82005-11-271
10MR92005-11-282
11MR102005-11-293
12MR112005-11-304
13MR122005-12-015
14MR132005-12-026
15MR142005-12-037
16MR152006-12-04373
Sheet1

Thanks, that works...but now I realise that I have to complicate things a bit more.

I just realized that with using the advanced filter to get a list of everyone that has expired, I would have to run this every day I open the file (as people would be expiring every day)...Is there a way to get it to search the data for everyone who has expired or going to expire and report a list that will always be current and not have to be re-run daily?

Cheers,
Yuee

Yuee said:
Thanks, that works...but now I realise that I have to complicate things a bit more.

I just realized that with using the advanced filter to get a list of everyone that has expired, I would have to run this every day I open the file (as people would be expiring every day)...Is there a way to get it to search the data for everyone who has expired or going to expire and report a list that will always be current and not have to be re-run daily?

Cheers,
Yuee
Hi Yuee:

As you begin to work on it ... you will find there are several ways of doing what you want to accomplish. Following is an illustration of using Conditional Formatting that highlites all the Member names whose membership has expired as of TODAY (the current day) ...
Book2
ABCD
1Nameend
2MR111/20/2005
3MR211/21/2005
4MR311/22/2005
5MR411/23/2005
6MR511/24/2005
7MR611/25/2005
8MR711/26/2005
9MR811/27/2005
10MR911/28/2005
11MR1011/29/2005
12MR1111/30/2005
13MR1212/01/2005
14MR1312/02/2005
15MR1412/03/2005
16MR1512/04/2006
Sheet13 (2)

I have used Conditional Formatting for cell B2 ...

formula is ... =\$B2-TODAY()<0 ... color Yellow

Now this highliting would automatically adjust every day as TODAY date changes. Now you act on the highlited list of names every day.

I f you need to incorporate features like highliting those whose membership has been expired for 7 days, or more than 30 days, ... you can do that as well.

Yuee said:
Thanks, that works...but now I realise that I have to complicate things a bit more.

I just realized that with using the advanced filter to get a list of everyone that has expired, I would have to run this every day I open the file (as people would be expiring every day)...Is there a way to get it to search the data for everyone who has expired or going to expire and report a list that will always be current and not have to be re-run daily?

Cheers,
Yuee
This has 2 lists, 'Expired' and 'Next 31 days' (meaning will expire in the next 31 days). The lists could be combined into 1 if desired.

1. C2 (copied down) is: =B2-TODAY()
2. F1 (counts how many have expired) is: =COUNTIF(\$C\$2:\$C\$16,"<=0")
3. I1 (counts how many will expire in the next 31 days) is: =COUNTIF(\$C\$2:\$C\$16,"<=31")-COUNTIF(\$C\$2:\$C\$16,"<=0")
4. E2 (copied down): =IF(ROW()>1+\$F\$1,"",INDEX(\$A\$1:\$A\$16,SUMPRODUCT(SMALL(--(\$C\$2:\$C\$16<=0)*ROW(\$C\$2:\$C\$16),-1+ROW()+COUNTIF(\$C\$2:\$C\$16,">0")))))
5. H2 (copied down): =IF(ROW()>1+\$I\$1,"",INDEX(\$A\$1:\$A\$16,SUMPRODUCT(SMALL(--(\$C\$2:\$C\$16>0)*--(\$C\$2:\$C\$16<=31)*ROW(\$C\$2:\$C\$16),-1+ROW()+COUNTIF(\$C\$2:\$C\$16,"<=0")+COUNTIF(\$C\$2:\$C\$16,">31")))))
Mr Excel.xls
ABCDEFGHI
1Nameend dateexpExpired6Next 31 days5
2MR120/11/2005-6MR1MR4
3MR221/11/2005-5MR2MR8
4MR322/11/2005-4MR3MR9
5MR423/12/200527MR5MR10
6MR524/11/2005-2MR6MR14
7MR625/11/2005-1MR7
8MR726/11/20050
9MR827/11/20051
10MR928/11/20052
11MR1029/11/20053
12MR1114/02/200680
13MR1215/02/200681
14MR1316/02/200682
15MR143/12/20057
16MR1518/05/2006173
Expired Membership

Yuee said:
Thanks, that works...but now I realise that I have to complicate things a bit more.

I just realized that with using the advanced filter to get a list of everyone that has expired, I would have to run this every day I open the file (as people would be expiring every day)...Is there a way to get it to search the data for everyone who has expired or going to expire and report a list that will always be current and not have to be re-run daily?

Cheers,
Yuee
Book7
ABCDEF
12005-11-2607
2nameend dateidxExpiredBy
3MR12005-11-20 MR81
4MR22005-11-21 MR92
5MR32005-11-22 MR103
6MR42005-11-23 MR114
7MR52005-11-24 MR125
8MR62005-11-25 MR136
9MR72005-11-26 MR147
10MR82005-11-271
11MR92005-11-282
12MR102005-11-293
13MR112005-11-304
14MR122005-12-015
15MR132005-12-026
16MR142005-12-037
17MR152006-12-04
Sheet1

Formulas...

A1:

=TODAY()

C1 must house a 0.

C3, copied down:

=IF(B3<>"",IF((B3-\$A\$1>0)*(B3-\$A\$1<=31),LOOKUP(9.99999999999999E+307,\$C\$1:C2)+1,""),"")

If you want any expiration ( >0 ), remove the *(B3-\$A\$1<=31) bit.

E1:

=LOOKUP(9.99999999999999E+307,C1:C17)

E3, copied down:

=IF(ROW()-ROW(E\$3)+1<=\$E\$1,LOOKUP(ROW()-ROW(E\$3)+1,\$C\$3:\$C\$17,\$A\$3:\$A\$17),"")

If so desired/needed...

F3, copied down:

=IF(E3<>"",LOOKUP(ROW()-ROW(F\$3)+1,\$C\$3:\$C\$17,\$B\$3:\$B\$17)-\$A\$1,"")

If you are on Excel 2003, all this can be made to run automatically to the full extend.

Replies
1
Views
65
Replies
3
Views
140
Replies
2
Views
275
Replies
3
Views
167
Replies
1
Views
434

1,206,755
Messages
6,074,755
Members
446,083
Latest member
kfunt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back