| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How 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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to format a cell to make it blank if it's content is "N/A"
Use ISNA around your formula returning the N/As, eg:
=IF(ISNA(yourformula),"",yourformula) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Turk" wrote: Dear all, How to format a cell to make it blank if it's content is "N/A" Thanks Turk |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to format a cell to make it blank if it's content is "N/A"
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" ... Use ISNA around your formula returning the N/As, eg: =IF(ISNA(yourformula),"",yourformula) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Turk" wrote: Dear all, How to format a cell to make it blank if it's content is "N/A" Thanks Turk |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to format a cell to make it blank if it's content is "N/A"
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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Turk" wrote: Thank you Max, However my formulae will be a bit too long if using your method, any alternate way to do it please? Turk |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to format a cell to make it blank if it's content is "N/A"
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" ... 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 .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Turk" wrote: Thank you Max, However my formulae will be a bit too long if using your method, any alternate way to do it please? Turk |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to format a cell to make it blank if it's content is "N/A"
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 --- On Mar 8, 3:43 pm, "Turk" wrote: 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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to format a cell to make it blank if it's content is "N/A"
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 FormatConditional Formatting Formula Is: =ISNA(A1) Set the font color to be the same as the fill color OK out Biff "Turk" wrote in message ... 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" ... Use ISNA around your formula returning the N/As, eg: =IF(ISNA(yourformula),"",yourformula) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Turk" wrote: Dear all, How to format a cell to make it blank if it's content is "N/A" Thanks Turk |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to format a cell to make it blank if it's content is "N/A"
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" bl... 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 FormatConditional Formatting Formula Is: =ISNA(A1) Set the font color to be the same as the fill color OK out Biff "Turk" wrote in message ... 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" ... Use ISNA around your formula returning the N/As, eg: =IF(ISNA(yourformula),"",yourformula) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Turk" wrote: Dear all, How to format a cell to make it blank if it's content is "N/A" Thanks Turk |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I make cells "If cell is blank then cell=0"in Excel 2000? | Excel Discussion (Misc queries) | |||
| Combining formulas, "and" & "or" to verify content of multiple cel | Excel Discussion (Misc queries) | |||
| Can you use a formula to make a truly "blank" cell | Excel Worksheet Functions | |||
| how can I make an excel cell "mark" or "unmark" when clicked on? | Excel Discussion (Misc queries) | |||
| Cond Format & helper-cell based "duplicate rec" tricked by content | Excel Discussion (Misc queries) | |||
Hybrid Mode
