How to suppress #VALUE!

[Philip]

After setting up functions, the spreadsheet appears ugly when there are
empty input cells. Is there any method in suppressing the display of
#VALUE! etc, say assigning white colour to these error words? Thanks!

 

[Ken Wright]

Fix the errors, dont just hide them, eg

Instead of your_formula that appears to give you the error when an input
cell is empty (assume it is A1), try

=IF(A1="","",your_formula)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Philip]

Thank you, Ken.
Your advice proved much better!

Cheers.

 

[ilia]

You could add conditional formatting where the "formula is":
=ISERROR(A1)
Then, set format to the background color of your worksheet for this
condition.

Add this conditional format in the A1 cell (or whichever the first cell
in the range where you expect errors that need to be hidden), then
paint the format to any other cells where you want this behavior.

As mentioned above, this is not the preferred handling of such an
issue. You're better off adding an
IF(ISERROR(formulaThatMightCauseError),"Error
Message",formulaThatMightCauseError) kind of error-checking to your
worksheet.


Philip \/\/|20+3:

 

[Philip]

Yes, my other project needs the hiding of error message otherwise the
appearance looks very untidy, in particular in its initial stage when
building up the necessary data.
For example, here is the formula and the cell holding it is at B37
Any missing data between B2 and B36 will cause error at B37

=ISERROR(INDEX(LINEST(B2:B36,$A$2:$A$36),2))

The result returned at B37 is TRUE instead of #VALUE!
Select B37 and then click Format /Conditional format at tool bar. But
couldn't find the background colour setting. Please advise path or setup
details. Thanks!

 

[Nick Hodge]

Philip

I would suppress this by formula not CF.

=IF(ISERROR(INDEX(LINEST(B2:B36,$A$2:$A$36),2)),"",INDEX(LINEST(B2:B36,$A$2:$A$36),2))

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk

 

[Philip]

Hi Nick,

Thank you. It serves the purpose I want.
This discussion board is very helpful.
Cheers!