On May 12, 3:22*pm, "Cimjet" wrote:
This is the link; *http://cjoint.com/?AEmvV7hxz6s

See my modified version at http://www.box.net/shared/x1r3a1mv03 .

Note: You can use that file as-is. But for the final "product", I
suggest that you copy the text of my macros and paste them into your
file.

Reason: When I saved the modified file on my system, I got some
warnings. I did not bother to think about the consequences of those
warnings, if any.

My point is: my file might not be identical to yours in all
respects. It would be better to use it as a prototype, not a final
"product".

My modified file contains 3 macros:

Workbook_SheetChange in ThisWorkbook:
- simply calls fillFormulas

fillFormulas in Module1:
- does all the work

chkFormulas in Module1:
- calls fillFormulas for all appropriate worksheets.
- "appropriate" means: I49 contains the word "Recorded".

If you choose to use chkFormulas, the SheetChange event macro might
not be needed. That's a judgment call -- yours.

The SheetChange event macro puts the formulas into only the worksheet
that caused the event -- that is, the changed worksheet -- if
necessary.

I put very few comments into the macros, in part to make them concise
and readable. I am hoping that the programming is clear.

If you have any questions, you can write to me at joeu2004 "at"
hotmail.com.

Essentially, fillFormulas put the appropriate formulas into I50:I54.
It is best to let Excel perform the computation, if possible.

When the formulas are in place, the SheetChange event macro will do
almost nothing. That makes the overhead of the event macro very
little.

Note that I guessed at what is an "appropriate" formula. I hope I got
it right. For each of I50:I54, I count the number of "I" for I50, "V"
for I51, "P" for I52 etc. I also count 1/2 the number of "½I" for
I50, "½V" for I51, "½P" for I52 etc.

Thus, for example, the formula in I50 becomes:

=COUNTIF(B6:AF17,"I")+COUNTIF(B6:AF17,"I")
+COUNTIF(B21:AF32,"I")+COUNTIF(B36:AF47,"I")
+ (COUNTIF(B6:AF17,"½I")+COUNTIF(B6:AF17,"½I")
+COUNTIF(B21:AF32,"½I")+COUNTIF(B36:AF47,"½I"))/2

I hope I guessed your intentions correctly. If not, send me the
correct formula to use for each of I50:I54, and I will make the
necessary change if you cannot do it yourself.

Hope this is a step in the right direction. Please let me know one
way or the other (email or a reply here).