Data Validation #VALUE

C

Cheryl

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.
 
D

Dave Peterson

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).
 
C

Cheryl

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.
 
D

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.
 
Top