 |
 | 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 / Worksheet Functions / March 2007How to format a cell to make it blank if it's content is "N/A"
Dear all, How to format a cell to make it blank if it's content is "N/A" Thanks Turk Use ISNA around your formula returning the N/As, eg: =IF(ISNA(yourformula),"",yourformula)  SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Dear all, > [quoted text clipped - 3 lines] > > Turk Thank you Max, However my formulae will be a bit too long if using your method, any alternate way to do it please? Turk "Max" <demechanik@yahoo.com> ¼¶¼g©ó¶l¥ó·s»D:BA78D4D1-1FB4-41CB-A08E-1E092C2E5DF5@microsoft.com... > Use ISNA around your formula returning the N/As, eg: > =IF(ISNA(yourformula),"",yourformula) [quoted text clipped - 5 lines] >> >> Turk Well, I suppose conditional formatting using ISNA would provide an alternative route to hide the ugly #N/As .. Select the formulas col (assume this is in col A) Then click Format > Conditional Formatting, and make the settings as: Condition 1, Formula is: =ISNA(A1) Format the font as white (to mask) > OK out Note that the underlying values masked are still #N/A, though .. SignatureMax Singapore http://savefile.com/projects/236895 xdemechanik --- > Thank you Max, > > However my formulae will be a bit too long if using your method, any > alternate way to do it please? > > Turk Thank you Max, Sorry that I can't blank the cells by using your method, my formula is =Vlookup(.....), if the ISNA(A1) command apply to Vlookup? thanks again Turk "Max" <demechanik@yahoo.com> ¼¶¼g©ó¶l¥ó·s»D:D79483D8-7EC4-44D4-BB47-923DB4B30551@microsoft.com... > Well, I suppose conditional formatting using ISNA would provide an > alternative route to hide the ugly #N/As .. [quoted text clipped - 11 lines] >> >> Turk Maybe try the CF using instead: Formula is: =ISERROR(A1) ISNA traps only #N/A error returns (the #N/A returns can be from vlookup, match, hlookup, etc) ISERROR traps all errors -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- > Thank you Max, > [quoted text clipped - 5 lines] > > Turk You could use conditional formatting to hide them. They'll still be there, you just won't be able to see them but if these results are used in other calculations this could be a problem. To use conditional formatting: Select the range of cells in question. Assume this is A1:A10 Goto the menu Format>Conditional Formatting Formula Is: =ISNA(A1) Set the font color to be the same as the fill color OK out Biff > Thank you Max, > [quoted text clipped - 14 lines] >>> >>> Turk Thank you Valko, Sorry that I can't blank the cells by using your method, my formula is =Vlookup(.....), if the ISNA(A1) command apply to Vlookup? thanks again Turk "T. Valko" <biffinpitt@comcast.net> ¼¶¼g©ó¶l¥ó·s»D:OweXAfGYHHA.3824@TK2MSFTNGP02.phx.gbl... > You could use conditional formatting to hide them. They'll still be there, > you just won't be able to see them but if these results are used in other [quoted text clipped - 27 lines] >>>> >>>> Turk |
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.