| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Complicated Challenge
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
Earl, Thank you for pointing out I am going to run into problems with the way I am trying to set it up. Can you explain how Access would be better suited for this? Oh maybe I should tell you that the managers that will be looking at these violation points have to have a simple format so they can just either look up a name or employee number and just look basically at total points to see who is a bigger risk at getting in or causing an accident. Yes I do want the points to devalue by month as the violation gets older so what is the best way to accomplish that? But I did enjoy the remark of adding the points up and just looking at him disgusted...I'll be sure to pass that one onto the safety managers. Thanks again for your help in this problem and wondering if this really going to work. Michelle -- lee6553 ------------------------------------------------------------------------ lee6553's Profile: http://www.excelforum.com/member.php...o&userid=15538 View this thread: http://www.excelforum.com/showthread...hreadid=387737 |
|
#4
|
|||
|
|||
|
Michelle,
The solution I described wouldn't be good for end users because it requires some steps be performed each time you want to get the total points for a perp. Yours would be easier, but suffers from the limit of violations it can hold. If you're willing do dig deeper into Excel, the steps of my solution could be automated with a macro, so the user need only indicate which perp is desired, then click a button to run the macro. Or the macro could prepare a list of all the perp's points. The Access solution can be made extremely easy for the user, but learning Access probably isn't as easy as learning Excel, though after you do, it's impressive what you can do with it with relatively little effort. So, I think, the Access/Excel decision rests largely on the importance of this application (is it worth having to learn Access or is there someone in the organization who could and would do it, or pay someone to get this done), and do the users have Access in the first place? -- Earl Kiosterud www.smokeylake.com "lee6553" wrote in message ... Earl, Thank you for pointing out I am going to run into problems with the way I am trying to set it up. Can you explain how Access would be better suited for this? Oh maybe I should tell you that the managers that will be looking at these violation points have to have a simple format so they can just either look up a name or employee number and just look basically at total points to see who is a bigger risk at getting in or causing an accident. Yes I do want the points to devalue by month as the violation gets older so what is the best way to accomplish that? But I did enjoy the remark of adding the points up and just looking at him disgusted...I'll be sure to pass that one onto the safety managers. Thanks again for your help in this problem and wondering if this really going to work. Michelle -- lee6553 ------------------------------------------------------------------------ lee6553's Profile: http://www.excelforum.com/member.php...o&userid=15538 View this thread: http://www.excelforum.com/showthread...hreadid=387737 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
Earl, My faith has been restored...thank you and just in time - life can go on now...It works wonderfully! At least until tomorrow when they say yeah but can such and such be added or they change something totally different. I will be sure to give credit where it is due - to you and your great knowledge! Thanks again so much! Michelle -- lee6553 ------------------------------------------------------------------------ lee6553's Profile: http://www.excelforum.com/member.php...o&userid=15538 View this thread: http://www.excelforum.com/showthread...hreadid=387737 |
|
#8
|
|||
|
|||
|
Michelle,
Good. Talk to you tomorrow. :) -- Earl Kiosterud www.smokeylake.com "lee6553" wrote in message ... Earl, My faith has been restored...thank you and just in time - life can go on now...It works wonderfully! At least until tomorrow when they say yeah but can such and such be added or they change something totally different. I will be sure to give credit where it is due - to you and your great knowledge! Thanks again so much! Michelle -- lee6553 ------------------------------------------------------------------------ lee6553's Profile: http://www.excelforum.com/member.php...o&userid=15538 View this thread: http://www.excelforum.com/showthread...hreadid=387737 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparison Challenge | Excel Discussion (Misc queries) | |||
| Complicated Formula | Excel Discussion (Misc queries) | |||
| How do you create a complicated pivot table graph? | Charts and Charting in Excel | |||
| Divide Ranks into two teams (mathematical guru challenge) | Excel Discussion (Misc queries) | |||
| CHALLENGE! - USING IF MAX MIN AND LOOKUP TOGETHER | Excel Worksheet Functions | |||
Linear Mode
