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: 445

Filed under:
|

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

Related posts about excel

Related posts about functions