excelexcel-formula

Using Excel rules to highlight a cell based on another cell information

What formula can I use in rules that can accomplish the following:

If there’s text on AA5 and Y5 its BLANK, then the cell matching from (D5 to I14) the number in cell W5 will be highlighted green

Goal

Similar to: =(AA5<>"")*(Y5="")

Solution

  • This formula is

    =SUM((D5=$W$5:$W$14)*NOT(ISBLANK($AA$5:$AA$14))*ISBLANK($Y$5:$Y$14))>0

    and the Applied to

    =$D$5:$I$14

    will format the required cells.
    You can replace the ISBLANK to a compare expression something like this:

    =SUM((D5=$W$5:$W$14)*($AA$5:$AA$14<>"")*($Y$5:$Y$14=""))>0