EXCEL generate a character in one column of a row, only if other columns in the same row are not bl
        Posted  
        
            by Simon
        on Super User
        
        See other posts from Super User
        
            or by Simon
        
        
        
        Published on 2010-03-25T01:49:32Z
        Indexed on 
            2010/03/25
            1:53 UTC
        
        
        Read the original article
        Hit count: 544
        
My speadsheet keeps track of when patients leave a specific floor of the hospital. There are columns in which where each patient goes is documented (1 column for "home", another column for "rehab facility", another for "other floor", etc.). Only when the patient leaves the hospital altogether does it count as a discharge, in which case the “discharge” column needs to have something in it.
What formula can I use to generate, say, an "x" in the "discharge" column if certain "where they went" columns in the same row contain something, but not if there is nothing in any of them?
Currently, to accomplish this I am using =IF(OR(M3>0,N3>0,O3>0,P3>0,Q3>0,R3>0,S3>0),"x") in row 3 of the "discharge" column (rows 1 and 2 are headings), and I have used "fill down" in all subsequent rows. To suppress the "FALSE" this formula yields when the condition is not true, I have applied conditonal formatting to the entire column that if the value=FALSE then the font is white (same colour as background).
Is there a more efficient, elegant and idiot-proof way of doing this? The conditional formatting of text colour could potentially confuse everyone but the person who built the spreadsheet (me).
© Super User or respective owner