Microsoft Office Forums - View Single Post - [Solved] Conditional Formula if a learner comes late in the morning, afternoon or morning and afternoon

p45cal · 11-24-2018, 07:24 PM

See attached. It's work in progress.
On sheet SF 2 JUNE HARD COPY, two buttons in the vicinity of cell AT9.
Update triangles will add triangles
Reset will take them away and show all text so that users can comfortably edit them.
What Update triangles does is:
Takes each row in the range E16:AI66
then takes each cell in that row then
will only look at cells having a diagonal line, then it will set the font colour to white, then, only if the cell does NOT have a formula in it:
it will look at the value in the cell, take out forward slashes, remove all trailing spaces and all but one leading space, convert multiple spaces to a single space and then look up what's left in a dictionary of 100 or so possibilities. That dictionary contains the definitions; things such as Absent AM, Late AM, Cutting AM, Absent PM, Late PM, Cutting PM, which will be TRUE if they apply. There's also a column (rightmost) containing a standardised version of the string in the cell which will replace what's in the cell if it's different from that standard version. Having found the entry in the dictionary, it then goes about adding coloured triangles, red for cutting classes and blue for lateness. If there are both in one morning or one afternoon, the cutting classes triangle is halved in size and placed on top of the late triangle. Then if there are any absences (Xs) any X in the cell's text is made visible again, made super- or sub-script to place it comfortably in the cell. If the dictionary lookup doesn't find anything, that cell is coloured yellow (so that you can experiment and find combinations which should be found in the dictionary - tell me what they are and I'll add them).
When each row has been processed, cells in columns AK and AL are updated.

Try it.
I will speed it up later.

You can get an idea of what's in the dictionary by running the macro PrintTable, which will add a new sheet. Look-up values are in the left column.

The general protocol for entering text in the cells is that a space separates am from pm data, as does TI and LE which replace the space (because TI and LE don't have a morning/afternoon attribute). Otherwise the order in which L, C and X appear shouldn't matter. Where you have the likes of LL, it's assumed, because you can't be late twice in the morning, that one L is for the morning, and the other for the afternoon. The same applies to XX etc.