Michelle,

I have been given more information - you know how these projects start
out...

Tolja!

Since you'll be preparing the reports (not the doofuss managers who'll get
them), we'll go with the Excel solution.

Sheet "Codes":

Code Violation Point Value Duration
A finger 1 3
B Pick nose 3 4
C Run light 2 5
D Vote Republican 4 6

etc.

The duration is the period of the violation in months. After the duration
has passed, the point value will have depreciated to zero.

A range name "Violations" must be defined (Insert - Name - Define). I've
defined it as referring to A2:D65536 in the Codes sheet.

Sheet "Violations":

If your newsreader or browser isn't using a non-proportional font like
Courier, this won't line up well.

A B C D
1 Name Code Date Aged points
2 John A 1/19/05 (formula)
3 John C 2/20/05 (formula)
4 Mary B 3/20/05 (formula)
5 John A etc.

the (formula) cell in D2:

=MAX(0,VLOOKUP(B2,Violations,4,FALSE)-DATEDIF(C2,NOW(),"m"))/VLOOKUP(B2,Violations,4,FALSE)*VLOOKUP(B2,Violatio ns,3,FALSE)

It's a bit of a nighmare. You can copy/paste it from this post (remove the
line spaces). I developed it in separate cells, then combined them. Copy
it down with the Fill Handle.

It should calculate the aged points as of the current date. Press F9 to
ensure that it's done a fresh calculation for today's date. Once it's
providing the correct aged points, sort the table on the name column (to get
like names grouped). Data - Subtotal, Each change in: Name, Subtotal: Aged
points. This will give a detail list, with group totals. You can click the
little 2 at the top of the outline symbols at left to collapse it to show
only totals by name. It will print however you have it looking at the time.

You can remove the subtotals with Data - Subtotals - Remove. You can add
records at the bottom of the list, then sort again when you're ready for
another report.

Thanks again for your help in this problem and wondering if this really
going to work.

Oh, ye of ...
--
Earl Kiosterud
www.smokeylake.com

"lee6553" wrote in
message ...

Earl,
I have been given more information - you know how these projects start
out...

I am going to be the keeper of the information (setting up, gathering,
tracking, etc.) whether it be in Excel or Access and I have knowledge
(not extensive but understand macros, functions, queries and so forth)
with both programs. I am just going to provide the managers with a
list of the highest perps. I do need help with getting it setup though
and of course after they meet with the sample, I'm sure it may
explode....
Thanks
Michelle


--
lee6553
------------------------------------------------------------------------
lee6553's Profile:
http://www.excelforum.com/member.php...o&userid=15538
View this thread: http://www.excelforum.com/showthread...hreadid=387737