Return a value if a range of cells contain text
Hey nice Excel people
I need what should be a pretty straightforward formula, but it's not turning out to be as easy as I thought . . .
Here's what I'm trying to do:
I have a range of cells (B3,B5,B7,B9 and B11) that start out blank and are validated so users can only select one of 4 responses:
<BLANK
RED
AMBER
GREEN
I want to put some kind of function in cell A2 so that if any one of the B range cells contains text, A2 shows Y - and if not, it's blank.
I can do it for one cell in a very long-winded way. In cell A2, I use the function:
=IF(OR(E11="RED",E11="AMBER",E11="GREEN"),"Y","")
but things get weird when I try to list multiple cells or use IF(OR) in combination. Also, I'm not convinced listing the possible responses is the most practical solution - again, all I really need to do is put a Y in A2 if any (or multiple/all) cells in the B list contain text.
I'm sure it can't be that hard, can it . . . what am I doing wrong?
Incidentally, it's Excel 2003 if that helps.
Thanks in advance.
Neil
|