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

Do the cells B2 - H2 have to be merged?

This formula works on unmerged cells, {=IF(N5:N9="no","message","")}

merged cells don't like array formulas.

Best I can do I'm afraid, perhaps a real expert can help...........


--
tghcogo
------------------------------------------------------------------------
tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494
View this thread: http://www.excelforum.com/showthread...hreadid=491466

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

They don't necessarily have to be merged--but the wording in it is too big
for one cell and I didn't know if it would automatically overflow if it was
text that just appeared. On the spread sheet that I am trying to duplicate
they also used merged cells for the message.

I am not sure though, how to have the message only appear when no is
selected...I have tried all kinds of different things and it always stays
visiable on the screen.


"tghcogo" wrote in
message ...

Do the cells B2 - H2 have to be merged?

This formula works on unmerged cells, {=IF(N5:N9="no","message","")}

merged cells don't like array formulas.

Best I can do I'm afraid, perhaps a real expert can help...........


--
tghcogo
------------------------------------------------------------------------
tghcogo's Profile:
http://www.excelforum.com/member.php...o&userid=10494
View this thread: http://www.excelforum.com/showthread...hreadid=491466

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

ET

Info only.

You can't put any more text into 10 merged cells that you can put into one
single cell.


Gord Dibben Excel MVP

On Wed, 7 Dec 2005 10:53:00 -0500, "ET" wrote:

They don't necessarily have to be merged--but the wording in it is too big
for one cell and I didn't know if it would automatically overflow if it was
text that just appeared. On the spread sheet that I am trying to duplicate
they also used merged cells for the message.

I am not sure though, how to have the message only appear when no is
selected...I have tried all kinds of different things and it always stays
visiable on the screen.


"tghcogo" wrote in
message ...

Do the cells B2 - H2 have to be merged?

This formula works on unmerged cells, {=IF(N5:N9="no","message","")}

merged cells don't like array formulas.

Best I can do I'm afraid, perhaps a real expert can help...........


--
tghcogo
------------------------------------------------------------------------
tghcogo's Profile:
http://www.excelforum.com/member.php...o&userid=10494
View this thread: http://www.excelforum.com/showthread...hreadid=491466

ET Wrote:
-They don't necessarily have to be merged--but the wording in it is too
big
for one cell and I didn't know if it would automatically overflow if it
was
text that just appeared.-


Providing the cells to the right of the "message cell" are blank, text
will overflow these adjacent cells. If you enter anything into the
adjacent cells, the text will be hidden/covered up, by those cells

You mentioned conditional formatting before, you could format the No
comment to have a different colour font and/or background when No is
selected. It depends what you are trying to achieve.


--
tghcogo
------------------------------------------------------------------------
tghcogo's Profile: http://www.excelforum.com/member.php...o&userid=10494
View this thread: http://www.excelforum.com/showthread...hreadid=491466

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