Making formula result #NA invisible?

[Phil C]

Hi All

I want to make my #NA values invisible in a table, from which a chart is
produced. I have to use NA(), not "", or else the chart doesn't plot
correctly. The error values are produced (when a referenced cell is blank)
by a formula of the form: =IF(B40="",NA(),24*60*(B40-$C$14))

Diane posted essentially the same problem on 7 August and got a 'workaround'
suggestion from DVT as follows:

"You could set your formula to return a #NA, then use conditional formatting
to make the text "invisible" in that case. The conditional formatting would
go something like this: Select cell A1, Format | Conditional Formatting |
Formula is | =NA(A1). Then set the font color to the same as the background
color (i.e. white). You can copy that cell and Paste Special | Formats".

I have tried this but it doesn't seem to work. The text in the cell stays
black when I have formatted it as brown (IF condition as above). I have
input the suggestion literally. If I type NA( in the dialogue box then
click on the cell in question, followed by ) , then OK, Excel puts
="NA($C$40)" in the box. C40 is the cell in question. Have I misinterpreted
the suggestion, or is it just wrong? I have tried vaious things using Cell
Value Is (e.g. equal to..."#NA"), but to no avail.


Thanks in advance, Phil

 

[Tushar Mehta]

If you want to format cell C5, in the Conditional Formatting dialog
box, set the first drop down to 'Formula Is' In the text box enter
=ISNA(C5). Note the presence of the equal sign and the *absence* of
any $ sign.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions