Lottery Syndicate Sheet to Show Members Behind In Payments

H

hiitsjack

New Member
Joined
Oct 16, 2010
Messages
10
Hi All,

I've created a spreadsheet for a lottery syndicate (20 members), the cells in column A has the Member Names (1st name in A2)and the cells in row 1 has the weekly dates (1st date "12/10/2010" is in B1).

When a member makes a payment ($10/ week), I open the spreadsheet and write "10" in the which ever the next empty weekly cell is.

I'd like to be able to tell at a glance which members are falling behind in their payments and have just come across "conditional formatting".

The formula needs to see If the cell for a given date is empty (I've not entered 10 in it) and if it's also within 28 days of today then I'd like the entire row (not just the cell) to change red.

I cant get it to work, any ideas ?

This seems a great feature and I hope it can do what I want here, I'll just have to play around until I stumble on the right formula.

Jack
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
Excel Workbook
ABCDEFGH
19/5/20109/12/20109/19/20109/26/201010/3/201010/10/201010/17/2010
2Tom101010101010
3Jim10
4Sam10101010
...

You want something like this? (Note: The entire row gets colored. It just doesn't display that way on this post)

Select all the rows with Names in it. (In this example rows 2, 3, and 4)
Select Conditional Formatting from the menu

Condition 1:
Formula is =TODAY()-INDEX($1:$1,1,MATCH(9.99999999999999E+307,2:2,1)+1)>=28
Select a red background color pattern


The INDEX($1:$1,1,MATCH(9.99999999999999E+307,1:1,1)+1) portion of the formula looks for the first unused cell in a row and returns the date from row 1 in the same column of that cell. The rest of the formula subtracts that date from today's date, and if it's more than 28 days, the row turns red.
 
Upvote 0
Hi AlphaFrog,

Thanks mate but I must be doing something wrong as nothing happens.

As you said in your post

By the way I'm using excel 97.

- I select rows 2 through to 21. (which covers all members)
- I select conditional formatting.
- I select "New Rule"
- I choose "Use A Formula To Choose Which Cells To Format"
- I copy and paste the formula as you've written - is that right?
- I press the "Formatt" button and choose the red background and hit OK.
- I then hit OK again (I've also tried hitting "Apply".

and nothing happens.

What am I doing wrong?

Hey, just a thought - I have the dates entered as day/month/year, would that make a difference?

Jack
 
Last edited:
Upvote 0
I thought it might be worth noting:

My current sheet has 3 members that have no entries to their names, they are located at A8, A18 and A21.

I've playing with the formule provided by AlphaFrog and if I change the > sign for a < sign then all rows go red except for the guys who havn't made a payment yet.

We're close.

Jack
 
Upvote 0
I've got my sheet working by changing the formula around to this

=INDEX($1:$1,1,MATCH(9.99999999999999E+307,2:2,1)+1)-TODAY()>28

As this formula seems to do the opposite of what I wanted ie: changes the row colours of members who ARE up to date to red - I had this change the rows to green and then I formatted all the cells to be red to start with.

I'd still like to get a better understanding of it but atleast it works and I think it looks great.

Thanks
 
Upvote 0
ganimachani said:
Can anyone please send me the link to download this spread sheet
Click to expand...
Hi ganimachani, Welcome to the MrExcel forums!

First of all, this thread is nearly 10 years old. It's very likely that the spreadsheet doesn't exist anymore. Next, the original poster said that he was using Excel 97. Conditional Formatting has changed a LOT since then, and since you have Excel 2010, that spreadsheet might not help you anyway. And finally, it's usually not a good idea to add a question to an existing thread, especially one so old. It's very possible that no one will notice your post.

I'd recommend starting a new thread, and just explain what you want to do, and I'm sure someone will help you out.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question
Showing number of payments left if start date and total number of payments are known
Replies
1
Views
379
SanjayGMusafir
SanjayGMusafir
T
  • Solved
Weighted lottery help?
2
Replies
14
Views
2K
bebo021999
B
S
  • Question
VBA Challenge
Replies
2
Views
238
SnowyBry
S
H
  • Solved
Coloring rows based on identical dates
2
Replies
15
Views
393
HowToDoIt
H
I
  • Question
Tracking vacation requests
Replies
9
Views
439
KRice
K

Forum statistics

Threads
1,214,907
Messages
6,122,181
Members
449,071
Latest member
cdnMech

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top