Can I Designate Cells "Not Available" Without Raising an Error?

[TC]

In earlier versions of Excel, the #N/A value was a useful way of
communicating "value not available". All cells derived from #N/A were
also designated #N/A.

In Excel 2003, #N/A works the same way, except that Excel now marks
all #N/A cells as errors. (i.e. Excel puts a green mark on the cell
and offers help.)

In my application, #N/A is not an error, and I don't want #N/A cells
to be marked as errors. Is there any way to make that happen?

(By the way, I know I can remove error designations by telling Excel
to ignore them, but that isn't a good solution in this case because my
code would need to parse the entire workbook for #N/A, just to ignore
errors, every time the user makes a change.)


-TC

 

[Dave Peterson]

I'm using xl2003.

If I type #n/a in a cell formatted as General (anything but Text), excel will
convert it to #N/A.

If I preformat the cell as Text or prefix it with an apostrophe: '#n/a
then excel leaves it as text and ignores it.

=sum() will treat those first #n/a (converted to #N/A) as errors. It'll treat
the second (text versions) as text and ignore them.

I'm not sure how the #n/a gets added to your cell in your case, though.

 

[TC]

Dave,

Thanks for the feedback.

To recap, I'm looking for a value I can put into cells which will
convey the meaning of "Not Available" for the cell and all cells
derived from that cell, yet will not cause the cells to be designated
as errors. In Excel 2000, the value #N/A used to do that, but in Excel
2003 #N/A is designated as an error by Excel's helpful (sarcasm) error-
checking feature, and I'm trying to avoid that.

As you reaffirmed, neither #N/A nor any text value like "#N/A" behaves
the way I want. Therefore, I still have no solution.


-TC

 

[Dave Peterson]

I didn't think I confirmed that.

I was trying to explain that if I entered '#N/A (with the leading apostrophe),
then excel saw it as plain old text.

It didn't get treated as an error.

=iserror(a1)
returned False when I did this.