Data Validation #VALUE
 

Even though I have data validation to accept only whole numbers, if a user
accidentally enters text, other cells that contain formulas based on this
cell change to #VALUE. The text is not allowed to be entered in the cell,
but the formulas in the other cells apparently read that text.

Don't the #value's only momentarily show up. Then you get the warning message
from the Data|Validation check.

Then as soon as you hit Retry or Cancel, the #values turn back to the previous
value?

I wouldn't worry about it, but you could change your formulas...

=if(istext(a1),"",yourformulahere)

Just to hide those #values (for just a moment).

Cheryl wrote:

Even though I have data validation to accept only whole numbers, if a user
accidentally enters text, other cells that contain formulas based on this
cell change to #VALUE. The text is not allowed to be entered in the cell,
but the formulas in the other cells apparently read that text.

--

Dave Peterson

No - the #VALUE's stay there - I think because there are circular
calculations, so can't correct itself. That's why I was using Data
Validation, but as of right now, not helping - since even though not allowing
that input, the other cells are still reading/using and coming up with the
error.
Thank you.

"Dave Peterson" wrote:

Don't the #value's only momentarily show up. Then you get the warning message
from the Data|Validation check.

Then as soon as you hit Retry or Cancel, the #values turn back to the previous
value?

I wouldn't worry about it, but you could change your formulas...

=if(istext(a1),"",yourformulahere)

Just to hide those #values (for just a moment).

Cheryl wrote:

Even though I have data validation to accept only whole numbers, if a user
accidentally enters text, other cells that contain formulas based on this
cell change to #VALUE. The text is not allowed to be entered in the cell,
but the formulas in the other cells apparently read that text.

--

Dave Peterson

Maybe changing the calculation to manual would help--the #values didn't show up
for me--but you do have to remember to calculate the workbook before you trust
it.

Cheryl wrote:

No - the #VALUE's stay there - I think because there are circular
calculations, so can't correct itself. That's why I was using Data
Validation, but as of right now, not helping - since even though not allowing
that input, the other cells are still reading/using and coming up with the
error.
Thank you.

"Dave Peterson" wrote:

Don't the #value's only momentarily show up. Then you get the warning message
from the Data|Validation check.

Then as soon as you hit Retry or Cancel, the #values turn back to the previous
value?

I wouldn't worry about it, but you could change your formulas...

=if(istext(a1),"",yourformulahere)

Just to hide those #values (for just a moment).

Cheryl wrote:

Even though I have data validation to accept only whole numbers, if a user
accidentally enters text, other cells that contain formulas based on this
cell change to #VALUE. The text is not allowed to be entered in the cell,
but the formulas in the other cells apparently read that text.

--

Dave Peterson


--

Dave Peterson