 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006How to suppress #VALUE!
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! 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)  SignatureRegards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- > 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! Thank you, Ken. Your advice proved much better! Cheers. > Fix the errors, dont just hide them, eg > [quoted text clipped - 6 lines] >> empty input cells. Is there any method in suppressing the display of >> #VALUE! etc, say assigning white colour to these error words? Thanks! 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: > Thank you, Ken. > Your advice proved much better! [quoted text clipped - 20 lines] > >> empty input cells. Is there any method in suppressing the display of > >> #VALUE! etc, say assigning white colour to these error words? Thanks! 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! > You could add conditional formatting where the "formula is": > =ISERROR(A1) [quoted text clipped - 38 lines] >> >> empty input cells. Is there any method in suppressing the display of >> >> #VALUE! etc, say assigning white colour to these error words? Thanks! 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)) SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk > Yes, my other project needs the hiding of error message otherwise the > appearance looks very untidy, in particular in its initial stage when [quoted text clipped - 52 lines] >>> >> #VALUE! etc, say assigning white colour to these error words? >>> >> Thanks! Hi Nick, Thank you. It serves the purpose I want. This discussion board is very helpful. Cheers! > Philip > [quoted text clipped - 59 lines] >>>> >> #VALUE! etc, say assigning white colour to these error words? >>>> >> Thanks!
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.