Hi,

I am surprised since I tested the formulas before posting them for you. Is
it possible that there are one or more space characters before or after the
"No"s (for example, the formula wouldn't count " No","No ", or " No " as
"No")? To check that, choose the "No"-option from the drop-down menu for the
cells in N5-N9; enter the formula =len(N5) in another say O5 and autofill the
formula to O9; the formulas should return 2; a larger number indicates there
are space characters by the side of "No"s.
To answer your second question, you can also use "Conditional Formatting" to
accomplish what you want, by doing the following:
Enter your message in B2 -- right click on the cell -- "Format Cells" --
in the "Font" Tab select the font color to white (a color that would merge
with the cell color and therefore wouldn't show up) -- "OK". Now you will
not see your message.
Now click on B2 -- "Format" -- "Conditional Formatting" -- with the
drop-down menu select "Formula Is" and enter the formula
=COUNTIF(N5:N9,"No")0 -- "Format" button -- "Font" Tab, select "Automatic"
for font color -- "OK" -- "OK".

Note that you will still have problem with the formula, if the "No"s are
associated with space characters.

Regards,
B. R. Ramachandran


"ET" wrote:

I would like the messge to appear if a NO is selected in any of the cells
N5 - N9.

I did try the formula for Case 2 (below) and now the message is totally
gone--does not appear at all.

I looked on the spreadsheet that I am trying to duplicate it from on the
formula is

=COUNTIF(checkRange,"NO")0

However, on that spreadsheet there are several columns that if no is
answered the user will get the message. But I do notice that there is no
actual "message" typed with the formula???


"B. R.Ramachandran" wrote in
message ...
Hi,

Do you want the message to appear when "NO" is answered in ALL the 5 cells
N5:N9 (Case 1) , or in ANY one or more (including all) cells in that range
(Case 2)?

Anyway, enter one of the following formulas in B2 (actually merged
B2:H2)depending on what you want.

Case 1: =IF(COUNTIF(N5:N9,"No")=5,"Your Message","")
Case 2: =IF(COUNTIF(N5:N9,"No")0,"Your Message","")

These formulas assume that the 5 cells, N5 thru N9, contain ONLY "Yes"s or
"No"s, and no other texts nor can they be empty (which I believe would be
the
case since only "Yes"s and "No"s would be the drop-down options)

A more complicated Case 2: The formula would become very complicated if
different messages were to appear (singly or in combination) depending on
which cell(s) contain "No"s.

Regards,
B. R. Ramachandran



"ET" wrote:

I am fairly new to Excel, but have searched and looked at the help menu
and
cannot figure out how to accomplish what I would like--but know it is
possible as I have another worksheet that does exactly what I want (sort
of).

In cells N 5 to N 9 I have a drop down menu with a Yes or No option.
If
they answer NO (only in those particular cells though, as I have other
drop
downs and no's are fine) I would like a pre-written message to appear in
merged cells B2 - H2. Otherwise the text would stay hidden.

I know it has to do with conditional formatting, and maybe linked cells?
but I can't seem to quite get it to work.

Any help would be muchly appreciated.

Elissa