View Single Post
  #1   Report Post  
the-chauffeur the-chauffeur is offline
Junior Member
 
Posts: 2
Default 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