View Single Post
 
Old 10-27-2018, 05:25 PM
Marcia's Avatar
Marcia Marcia is offline Windows 7 32bit Office 2007
Expert
 
Join Date: May 2018
Location: Philippines
Posts: 526
Marcia has a spectacular aura aboutMarcia has a spectacular aura aboutMarcia has a spectacular aura about
Default

Okay. We can not divide the day into 2 cells because according to my teacher friends whom we are helping to make their life easier every EOM, the day should be divided by a broken diagonal line. These are the codes in E16:AI28
am absent ( "X","XTI","XLE" left align) no shade, only the X letter is visible

pm absent ("X","XTI",XLE" right align) no shade, only the X letter is visible

whole day absent ("X"four spaces"X", "XTIX","XLEX") no shade, only the X letters are visible. 4 spaces so that the "X" will appear on the right and left of the cell area

Tardy ("L","LTI","LLE" left align) upper left triangle shaded, font size 4 so that the triangle will cover the letters

Whole day tardy (""LL","LLTI","LLLE") whole cell area shaded, font size 4
Cutting Classes ("C","CTI","CLE" right align) lower right triangle shaded, font size 4
Transfer Out ("T/O") white color font
Transfer In ("T/I") white color font
Drop Out ("DRP") white color font
Late Enrollee ("LE") white color font


I amended the last macro that you made, see attached sheet, but I came upon the "LE" problem. Another one is when a day is already marked by a formula as "LE" or "T/I", and should also be marked manually as absent, late or cutting classes, thus the multiple or combined codes XTI, XLE, LTI, LLE, CTI, CLE, XTIX, XLEX, LLTI, LLLE. Please also take notice of the formulas in Rows 21 and 26, Columns AJ and AI. Columns AJ and AI either count the absences or tardiness while Rows 21 and 26 count the learners in attendance daily. Cells C4 and C26 are the number of enrolees at the beginning of the school year. I hope I am making sense.

There will be a conditional formatting that will turn all the typed remarks except the remarks for absences ("X", "X X")into white font color, before printing the form, something that I learned how to do. The printed form will show only the Xes and the shades for late and cutting classes.
BTW, I asked for shaded absent, tardy and cutting classes, both am and pm earlier for my future references.


Is there a better way to shorten the very long formula in E21 to AI21?
Attached Files
File Type: xlsm JUNE 2018 QUERYB.xlsm (116.4 KB, 11 views)

Last edited by Pecoflyer; 10-30-2018 at 04:26 AM. Reason: Removed quote
Reply With Quote