I need to kinow:

1. All the codes which you want to interpret and what you want to appear when they're encountered.
2. If there are going to be multiple codes in one cell then I need to know all the possible combinations of such code and what you want to see when those combinations occur.

Bear in mind that currently, the single leftmost character in a cell determines what's shownfor the morning; anything after that (2nd character onwards) is going to be assigned to the afternoon. If that's no longer going to be possible then a rethink is required (on my part). You could, for example, instruct your users that everything to the right of a / character (and/or a \ character) applies to the afternoon; and if there is no such character everything in the cell applies to the morning. Alternatively, you could divide each day into 2 cells, the left cell relating to the morning and the right cell the afternoon.

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

JUNE 2018 QUERYB.xlsm (116.4 KB, 13 views)

I'd be interested to know how you do this!


I will look at this after we know for sure the sort of things that will appear in the cells above that formula. In the meantime, what version of Excel are you using? There are some newer functions which could simplify the formula a lot.

I am using Excel 2016. When and if a student skips classes (am) and is late (pm) on the same day, the late triangle should be placed on the left, the cutting classes shape on the right regardless of the time of misdemeanor, so only the codes LTIC, LLEC, LC should be used. The teacher will have a note somewhere in the form on additional info like the above cases. The same holds true with am cutting classes, pm absent, where the X is typed on the left, the cutting class shape placed on the right.

On the conditional formatting, I was thinking of doing the simple =E16="DRP" format color white, =E16="T/O" format color white. I was hoping that the macro will do the job of making the "LE", "T/I","TI" letters into white color font.

I didn't know that this form will turn out to be so complicated, but please don't give up on this because I know many others out there are following and monitoring the final output of this "project" given the number of views on record. Thanks.

I'd be a bit wary of this plan. You have a system where the upper left half of a cell is always the morning, and the lower right always the afternoon. Now you have to make an exception if there's a red triangle: you don't know if the lateness is in the morning or not, you don't know if the cutting classes is in the morning or not, the absenteeism could be morning or afternoon. I'd strongly recommend not doing this.
There are plenty of ways to indicate things in the cells while keeping the am/pm layout correct.
It looks as if cutting classes could be split am/pm, or perhaps you're happy for this to be represented without am/pm information? Either way there are plenty of ways you might represent this in a cell - it's down to your imagination.
For example, you might use one of these to show the student has been cuttting classes sometime on a day (no am/pm nformation):




Instead, you could keep am/pm information this way:


It's fairly obvious what's happening.

I fully agree with you that it i confusing to make an exception on the representation of cutting classes, always shaded triangle on the right. It is clearly a contradiction on the rest of the coding system but it is the instruction from the higher ups. I suggest we maintain the status quo as is the instruction. Although there is plan to make representations before the approving authorities that we make our own symbol as long as the figures are correct. Could you please make the macro as flexible as possible so that in case the powers that be would approve on our proposal the codes would be easier to amend?
If you must know, the knowledge of the users of this form on the power of Excel to help them in their work is pathetically nil. In fact some of them still use crayons to fill up the form and calculators to get the totals and average daily attendance. I am optimistic that once we present to the powers that be that it is possible to reflect in the form all the conditions in the proper formats of am and pm through the use of different colors and symbols, they will look favorably on such enhanced reports.

How are you going to display cutting classes am, late am, absent pm?

Besides being significantly more convoluted to code for, it'll confuse users trying to get what they want - they'll need the contradictions/exceptions explained, and it'll be more difficult to get the row and column totals correct (If the letters L, X & C in a cell all represent half days (not counting the L in the LE pair), then counting occurrences of these letters makes for simpler and more robust totals formulae.)

The 'authorities', 'powers that be', 'the higher ups', whatever, haven't put the thought into this that you (and I) have; their decisions are often ill-informed and ill-advised, sometimes they're just bloody-minded and want to show who's boss. It's up to you to advise and inform them what's best. At the moment I'm leaning strongly towards my last suggestion of the smaller triangles on top of the larger ones (If you want, we could even keep ALL cutting classes small red triangles, regardless of whether they're sittting atop another larger triangle, to maintain consistency).

I would prefer you to set things in concrete sooner rather than later - I don't want to have to revisit this too often. Remember, I'm doing this for nothing.

I think I might have to back-pedal a little on having something ready at the weekend, something's come up and it's already Friday afternoon here in the UK. Next week's very busy for me too. So you'll need patience, and I'm not giving up and I've made good progress.

I moved this post to Excel Programming.