Re-posted need more detail

  • Thread starter Thread starter Gav !!
  • Start date Start date
G

Gav !!

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 !!
 
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
Click to expand...
code than formulas, could someone please elaborate for me.
 
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
 
Back
Top