How to create every month collector on 1st of month

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

alan83m

Thread Starter
Joined
Dec 24, 2012
Messages
4
I am a i nsurace agent, i have to collect the premium with my client every month. Every time need to check 1 by 1 is tired.
i also find many tutorial but still not understanding the step. if any can help just to help. can be the due date client put in a notice boxes?

Here is my file:
 

Attachments

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,478
did you want to use conditional formatting on say the name - so it checks todays date - v due date v if paid already and change colour

you have also listed in a public forum, names and policy numbers - available for everyone to use

I will remove the file - if you could reload with dummy data - sorry for inconvenience if this was already dummy data - but not made clear
 

alan83m

Thread Starter
Joined
Dec 24, 2012
Messages
4
Sorry about that, I just wan the due date by that month only,but want repeat every month.
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,478
I just wan the due date by that month only,but want repeat every month.
not sure what exactly you want to happen -

can you post some dummy data for people to review

it would be simple to have a conditional format of the name to be highlighted if todays day is the same as the policy day
eg - if the policy is 3/2/11 then on the 3rd of each month the name could be highlighted and could stay highlighted for say a day or two - or change colour when over due

How would you know if paid ?

or it could just stay for a couple of days

conditional format to use

=IF(DAY(NOW())=DAY(E5), TRUE,FALSE)
and fill change colour for true

put into cell C5 as a conditional format and then copy > paste special > to C6 - C51

so now all people when the renewal day = todays day would change colour
and you could add between days if you want to keep the highlight longer

lots of other ways to do it based on if paid or not and based on the month
but to do that you need to
1) load dummy data in exact format and exact possible entries
2) details the rules to use
 

alan83m

Thread Starter
Joined
Dec 24, 2012
Messages
4
actually i dun know wat is dummy data, i find many solution can solve my problem, is just i dont know the formula of reminder.
i have another question for ask, can i do a form format for my file, example: sheet 1 is let me key in the info & sheet 2 will be display the info, can excel do that.
 

alan83m

Thread Starter
Joined
Dec 24, 2012
Messages
4
not sure what exactly you want to happen -

can you post some dummy data for people to review

it would be simple to have a conditional format of the name to be highlighted if todays day is the same as the policy day
eg - if the policy is 3/2/11 then on the 3rd of each month the name could be highlighted and could stay highlighted for say a day or two - or change colour when over due

How would you know if paid ?

or it could just stay for a couple of days

conditional format to use

=IF(DAY(NOW())=DAY(E5), TRUE,FALSE)
and fill change colour for true

put into cell C5 as a conditional format and then copy > paste special > to C6 - C51

so now all people when the renewal day = todays day would change colour
and you could add between days if you want to keep the highlight longer

lots of other ways to do it based on if paid or not and based on the month
but to do that you need to
1) load dummy data in exact format and exact possible entries
2) details the rules to use
How would i know i pay? so what i can do to solve this?
You give me the formula just show me the colour only, so what can i do if my client is pay to me?

What u mean that,sori coz i stilll new bird?
1) load dummy data in exact format and exact possible entries
2) details the rules to use
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,478
actually i dun know wat is dummy data,
data that is not real , for instance you have names and policy numbers - they should be made up policy numbers and made up names- not real people with real policies

i find many solution can solve my problem, is just i dont know the formula of reminder
dont understand the question, can you give the examples of the formula you want to understand

i have another question for ask, can i do a form format for my file, example: sheet 1 is let me key in the info & sheet 2 will be display the info, can excel do that.
yes , you would again need to be very detailed and explicit in what you would want to type on sheet 1 and how it appears in sheet 2

a simple way is to use

=sheet1!cell

on the 2nd sheet to refer to the cell on the forst sheet

so say you want whatver is in sheet 1 cell A2 to appear in sheet2 cell E2

then in E2 on sheet 2 type
=sheet1!A2
now what every you type in A2 on sheet 1 will appear on sheet2 cell e2

or you can have a much more sophisticated form that populates the sheet
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,478
You give me the formula just show me the colour only, so what can i do if my client is pay to me?
we can add that info as well

so need to know on the sheet - how do you represent when a payment has been made ?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top