Return a value if a range of cells contain text

[the-chauffeur]

Hey nice Excel peopl

I need what should be a pretty straightforward formula, but it's no
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 an
are validated so users can only select one of 4 responses:

<BLANK
RE
AMBE
GREE

I want to put some kind of function in cell A2 so that if any one of th
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 us
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) i
combination. Also, I'm not convinced listing the possible responses i
the most practical solution - again, all I really need to do is put a
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

Nei

 

[Claus Busch]

Hi,

Am Wed, 3 Oct 2012 08:53:34 +0000 schrieb the-chauffeur:

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.

try in A2:
=IF(COUNTA(B3,B5,B7,B9,B11)>0,"Y","")


Regards
Claus Busch

 

[the-chauffeur]

Hi,

try in A2:
=IF(COUNTA(B3,B5,B7,B9,B11)>0,"Y","")


Regards
Claus Busch

Claus

You're a genius - that's exactly what I was after.

Thanks so much for your time.



Nei