On Tue, 20 Jan 2004 19:27:50 -0600, "Jim" wrote:

Hi
I have a carpet-cleaning business. I have set up a spreadsheet as
follows:

Date of service First Name Last Name Address Price #
of Jobs Alert


By the end of the year I may have a 1000 names some of which may have been
done several times in the year. I want to be alerted when the LAST service
done for any name is over 6 months old and have it Alert me in the Alert
column. If a person had their carpets cleaned on Jan 5, Jun 10 and Nov 23, I
need a formula that will alert me only 6 months after the last date(Nov 23)
not 6 months after Jan 5 or Jun 10. Using Sumproduct I can get that I have
done 3 jobs for the person, but how can I get the 6-month alert to work for
only the last of the 3 dates.
I want to be able to send out reminder cards for those who have not been
serviced in the last 6 months. Any ideas? Thanks much! Jim



A lot depends on how you have your database set up.

Perhaps this will give you an idea of an approach.

Assume you have the customer names in A1:A1000 and the carpet cleaning date in
B1:B1000.

In a separate column, place a list of customers -- let us say that these
thousand jobs are done on 200 customers. Make this column F.

The last date that a particular customer had their carpet cleaned would be
given by the *array-entered* formula:

=MAX((F2=$A$1:$A$1000)*($B$1:$B$1000))

To *array-enter* a formula, hold down <ctrl<shift while hitting <enter. XL
will place braces {...} around the formula.

So you would enter the formula in G2 and copy it down as far as needed.

You could then use a conditional format on the cells that had the last date.

Format/Conditional Format/Formula Is: =TODAY()(G2+45)

If you select all the cells in column G before entering the formula, it will be
entered into each cell, and the cell reference adjusted appropriately. Or you
can enter it in G2 and use the format painter to copy it to the other cells.

I like to use a red fill with a bold white font to make it stand out.

Then, whenever you open that sheet and it recalculates, those who are over 6
months will stand out quite readily.


HTH,

--ron