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

Return a value if a range of cells contain text
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus

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

Thanks so much for your time.



Neil