Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-posted need more detail
I have been advised that this would be easier done in code than formulas, could someone please elaborate for me.
Folks Could someone tell me if I can do this without code or not. What I want to do is have a list of dates across row 1, say every tues and thurs in a month but continiuing across 1 sheet per month. Now I want to mark attendances in to row 2 for that person so it may be A2 is "A", B2 is "A" etc.. and have a monthly percentage and some form of ongoing percentage, but when I put in a value of say "E" for exam instead an "A" the percentage starts from zero again. The basic concept is that I keep a running tally of whether the person can be examined or not based on their attendance and once they have been examined, start the count and percentage again. Therefore if a person has over 85% attendance they can be examined or a count of 12 nights. Once they are examined the count and percentage rate would start again until they reach the same equivalent for their next exam. All help is greatly appreciated, all examples of code or formulas to kick me off more than welcome. Regards Gav !! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-posted need more detail
You don't say where the 'result goes.
Lest say that column A is the person's name, B is the result and C... the days. For each row, start at the right most cell and count the 'A's until you get to an 'E' . Place the result in B Set conditional formatting to make the background ob 'B' go green say for when an exam is possible. Code is simplistic - email me directly for working sample Patrick Molloy Microsoft Excel MVP -----Original Message----- I have been advised that this would be easier done in code than formulas, could someone please elaborate for me. Folks Could someone tell me if I can do this without code or not. What I want to do is have a list of dates across row 1, say every tues and thurs in a month but continiuing across 1 sheet per month. Now I want to mark attendances in to row 2 for that person so it may be A2 is "A", B2 is "A" etc.. and have a monthly percentage and some form of ongoing percentage, but when I put in a value of say "E" for exam instead an "A" the percentage starts from zero again. The basic concept is that I keep a running tally of whether the person can be examined or not based on their attendance and once they have been examined, start the count and percentage again. Therefore if a person has over 85% attendance they can be examined or a count of 12 nights. Once they are examined the count and percentage rate would start again until they reach the same equivalent for their next exam. All help is greatly appreciated, all examples of code or formulas to kick me off more than welcome. Regards Gav !! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Re-posted need more detail
Sometimes the easier solutions are harder to spot.
column A is name. columns C - IV are date columns where values may A,E or whatever. We need to count, starting from the right-most column and moving left all the 'A' values until we hit an E Here's a FUNCTION that does just that: Public Function Attendance(ThisRow As Long) As Long Dim cl As Long Dim count As Long With ActiveSheet cl = .Cells(ThisRow, "IV").End(xlToLeft).Column Do While cl 2 If .Cells(ThisRow, cl).Value = "A" Then count = count + 1 ElseIf .Cells(ThisRow, cl).Value = "E" Then Exit Do End If cl = cl - 1 Loop End With Attendance = count End Function add this to a standard module. All you do is pass the row number. so in the example, suppose A2 is John, in B2 put =Attenance(Row()) This passes the current row to the function, which counts the A's Patrick Molloy Microsft Excel MVP demo workbook available on request -----Original Message----- You don't say where the 'result goes. Lest say that column A is the person's name, B is the result and C... the days. For each row, start at the right most cell and count the 'A's until you get to an 'E' . Place the result in B Set conditional formatting to make the background ob 'B' go green say for when an exam is possible. Code is simplistic - email me directly for working sample Patrick Molloy Microsoft Excel MVP -----Original Message----- I have been advised that this would be easier done in code than formulas, could someone please elaborate for me. Folks Could someone tell me if I can do this without code or not. What I want to do is have a list of dates across row 1, say every tues and thurs in a month but continiuing across 1 sheet per month. Now I want to mark attendances in to row 2 for that person so it may be A2 is "A", B2 is "A" etc.. and have a monthly percentage and some form of ongoing percentage, but when I put in a value of say "E" for exam instead an "A" the percentage starts from zero again. The basic concept is that I keep a running tally of whether the person can be examined or not based on their attendance and once they have been examined, start the count and percentage again. Therefore if a person has over 85% attendance they can be examined or a count of 12 nights. Once they are examined the count and percentage rate would start again until they reach the same equivalent for their next exam. All help is greatly appreciated, all examples of code or formulas to kick me off more than welcome. Regards Gav !! . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question not posted? | New Users to Excel | |||
Why my posting are not posted! | Excel Discussion (Misc queries) | |||
Posts not showing up - posted through OE | Excel Worksheet Functions | |||
I need a eqivalent for function posted here | Excel Discussion (Misc queries) | |||
Hi ..I posted some more info ..... | Excel Discussion (Misc queries) |