excelconditional-formatting

Conditional formatting dependent on several variables


I have an Excel sheet that is meant to help with evaluating skills of employees. The sheet is formatted so that the employee names are in cells B1-AO1 and their respective job titles are in the row below that (so B2-AO2). The rows below that are the scores they have for several skills. From AQ1-BE1 are the job titles, the two rows below that are empty.

Shorter example of the Excel sheet

I now want to have conditional formatting that compares the skill cells of the employees to the skill cells for their job and highlights them red if they're lower and green if they're equal or higher. However, the job titles of people may change and we have a lot of employees, which is why I can't individually input the formatting.

So far I've done that by using AND($B$2=$AU$1, B4<AU4, AU4 <> "", B4 <> "") and AND($B$2=$AU$1, B4>=AU4, AU4 <> "", B4 <> ""). However, by using this I need to copy that for each column that is possible, which is quite a lot.

Is there any way to optimize this without using VBA? Or a way to to it in VBA that continuously updates? The sheet will be used by people who do not know anything about Excel so I'm trying to make it as easy to use as possible.


Solution

  • Suppose you have a setup like this for employees in A1 to D12

    Charlie Juliet Mike Oscar
    Sailor Tailor Soldier Tinker
    4 5 0 3
    4 1 1
    3 4 3
    2 1 3
    2 1 1 1
    4 4 2 5
    2 3 4 4
    1 3 5 4
    1 3 3 1

    and this for jobs (which may be in a different order of columns but same order of rows) in Q1 to T12

    Job1 Job2 Job2 Job4
    Tinker Tailor Soldier Sailor
    4 1 2 0
    4 3 2 3
    2 3 5 2
    5 2 3 2
    2 2 2
    5 4 4
    4 3 5 3
    4 1 1 3
    3 3 0

    You can try formulas

    =LET(req,@XLOOKUP(A$2,$Q$2:$T$2,$Q$4:$T$12),AND(req<>"",A4<>"",A4<req))

    and

    =LET(req,@XLOOKUP(A$2,$Q$2:$T$2,$Q$4:$T$12),AND(req<>"",A4<>"",A4>=req))

    This uses two tricks (but perfectly legitimate ones which are in the Microsoft documentation)

    (1) if you do a lookup in a single row but return a 2d range, you get a whole column out of that range see xlookup.

    (2) If you have a reference to a column and put an @ sign in front of it, you get just the element corresponding to the row that you're on see implicit intersection.