Michelle,
This will be fairly straightforward, actually. You're trying to do an
application that'd be easier in Access than in Excel, but so far, it won't
be too involved. But as you add requirements, that will change a bit.
You were doing fine until you wanted to repeat columns B and C several times
in the violations sheet. This causes problems, because sooner or later
someone comes along who accrues more violations than you have room for.
Then the very people who've accrued so many violations go around proudly
telling EVERYONE that the system couldn't keep track of all their missteps.
You're trying to mix a names table (one record per name) with a violations
table (one record per violation). I suggest you change the Violations sheet
to look like this: (If your newsreader isn't set up to display with a
non-proportional font like Courier, these columns won't line up well):
Name Code Date Effective Points
John A 1/1/2005 (formula)
Sally D 4/1/2005 (formula)
John C 2/23/2005 (formula)
etc.
This is a violations table, not a name table. One record per violation.
You'll find this database approach much more usable as you develop new
requirements (and they'll come along) for this project. The Effective
Points column would have a forumula to calculate the current points, based
on the duration from the Code sheet and the current date. It will involve
some VLOOKUPs and some DATEDIFs and stuff. I didn't develop such a formula
yet. You apparently want the points at the current date to devalue linearly
by month as the violation gets older, right? Pro-rated points. Weighted
points. Not just keep the total points, then drop off completely at the end
of the period for that violation, as is more usual (I think). Which?
Now for a total of points by name, do either a Pivot Table to summarize
them, or sort the violations table by name (so each like name is grouped),
then do Data - Subtotals.
With this approach, you could also easily make a detailed summary of any
given name's violations, which could (using VLOOKUP) list the description of
the violation, duration, current points, etc. in each violation item. It
could also total up the effective (weighted) points. Then you print it and
hand it to the perp, and look real disgusted. :)
--
Earl Kiosterud
www.smokeylake.com
"lee6553" wrote in
message ...
I am trying to set up a point violation spreadsheet. On my code sheet I
have in column A, letter codes, then in B is the violation and in C is
the point value and column d is the number of months the violation
stays on record. On my violation sheet, Column a is name, then b is
where I would enter the letter code from code sheet and then column c
is the date of the violation. The b & c columns are repeated in d&e,
f&g.... until column Z where that represents the total of all points.
I want to be able to enter on the violation sheet a code letter in
column b, d, f date of violation in c, e, g and then in column z I want
it to add the points that are assigned from the code sheet.
The other challenge is the points stay on their violation record for
so many months. So for instance if I enter code letter X it looks at
column d on the code sheet and sees it stays on the record for 36
months. So if a violation date entered in column c, e or g it would
need to lose 1/36 of the value from todays date and keep a running
total in column z on the violation sheet.
I'm not sure how or where to begin on this project (functions or
programming) or if it is possible so any help would be greatly
appreciated. I want to thank you in advance for your knowledge and
help.
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