How to find out empty cell reference?

Question

I have the following table:

    Roll No     Eng Hin Mar Math
    1            10      10  10
    2                10  10 
    3            10          10
    4                10  10 
    5                10  10 
    6            10  10  10 
    7                10  10  10
    8                10  10  10

I need to find out only blank cells and highlight only those students who are left blank. For example, Roll no 1 is blank at Hin column and Roll no 2 is blank at Eng and Math:

Roll No   Pending Sub
1         Hin
2         Eng & Math

Can anyone tell me how to get that result?

score 0 · Answer 1 · answered Oct 05 '15 at 04:12

I have changed the layout of the data in your OP to try to match it to your example and am interpreting "highlight" as more "draw attention to" than any particular formatting.

Assuming Roll No is in A1, please try in say G2 and copied down to suit:

 =LEFT(IF(ISBLANK(B2),B$1&" & ","")&IF(ISBLANK(C2),C$1&" & ","")&IF(ISBLANK(D2),D$1&" & ","")&IF(ISBLANK(E2),E$1&" & ",""),LEN(IF(ISBLANK(B2),B$1&" & ","")&IF(ISBLANK(C2),C$1&" & ","")&IF(ISBLANK(D2),D$1&" & ","")&IF(ISBLANK(E2),E$1&" & ",""))-3)

How to find out empty cell reference?

1 Answers1