How do I display a text message when a cell has a certain value??

[laureny55]

Hi,

I have a cell where if the cell is empty (A1=0), then I would like
the
cell to display "Enter your value here." Otherwise, I would like the
cell to display the value a person enters.


I have tried using an if/then/else statement and, understandably, I
get a circular reference error:


=IF(A1=0,"Enter your value here",A1)


Does anyone have a suggestion?


Thanks!

 

[RagDyeR]

Why not just enter the text itself?

The value entered by the user will displace (replace) it.

This should work if the form is a template.
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi,

I have a cell where if the cell is empty (A1=0), then I would like
the
cell to display "Enter your value here." Otherwise, I would like the
cell to display the value a person enters.


I have tried using an if/then/else statement and, understandably, I
get a circular reference error:


=IF(A1=0,"Enter your value here",A1)


Does anyone have a suggestion?


Thanks!

 

[laureny55]

Thanks for your response, RD... However, I want it to be so that
whenever someone enters 0 as a value, the message appears.

 

[Beege]

Thanks for your response, RD... However, I want it to be so that
whenever someone enters 0 as a value, the message appears.

laureny55

Have you looked at data/validation? It can "alert" the user when 0 is
used to use a different value...

Beege

 

[RagDyeR]

You could try "Data Validation"

Couple of approaches:

For a "one time shot",
Enter the text "Enter Value Here" in the cell, *then* click back in the cell
and hit:
<Data> <Validation>
In the "ALLOW" window choose "decimal",
In the "DATA" window choose "greater then",
In the "MINIMUM" window choose 0 (zero).

Click on the "Error Alert" tab, and in the "error" window type:
"Value must be greater then 0"
Then <OK>

The text will *only* display in the cell until a correct entry is made.
It will continue to be visible as long as *incorrect* entries are attempted.

OR

More enduring:

With cell empty, click on:
<Data> <Validation>
In the "ALLOW" window choose "decimal",
In the "DATA" window choose "greater then",
In the "MINIMUM" window choose 0 (zero).

Click on the "Input Message" tab, and in the "Message" window enter:
"Enter Value Here"

Click on the "Error Alert" tab, and in the "error" window type:
"Value must be greater then 0"
Then <OK>

NOW, clicking in the empty cell will bring up the input message, and an
incorrect entry will display the error message.
You could color this cell in order to emphasize it.

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Thanks for your response, RD... However, I want it to be so that
whenever someone enters 0 as a value, the message appears.

 

[laureny55]

Thank you!! Those were all great suggestions!

 

[Francis Hookham]

Assuming you are expecting numeric entries you could Custom Format the cells
something like this (depending on how you want the numerals to be
displayed):

#,##0;-#,##0;"Enter your value here"

(You could even format like this #,##0;-#,##0;[Red]"Enter your value
here")!!!!!!!

Nothing will appear until a positive or negative number or zero is entered
so, if you want to indicate the cells where entries are required, type in
"Enter your valve here" and right align the cell.

Francis Hookham

 

[Ragdyer]

You're welcome, and appreciate the feed-back.