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

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

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

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

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

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

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

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