make error values disappear (2003 SP2)

Topic

New Reply

Aloha folks,
I’m trying to find a way to make #N/A values invisible to the viewer. I’ve tried custom number formatting, conditional formatting, no luck. Those pesky #N/As keep showing.

In the attached sheet, the user pastes data into cells N3:O29, which is echoed in cells B3:C29 and thus shown on the chart. The formulas in cells C3:C29 return #N/A so that the price series on the chart can interpolate and won’t drop to 0. I don’t know of anything else that those formulas could return that would do the same thing — not 0, not “”, not any text value; all those make the price series drop to 0. If a cell such as C19 were blank, then the series could still interpolate, but for that I would need a macro solution and I’m trying to avoid that.

The sheet is printed to pdf format. The paste area is outside of the print area, so its appearance is not an issue.

Got any tricky tricks to make the #N/A values invisible?

Mahalo for your suggestions,
JohnJ

Reply | Quote

Replies

Why don’t you exchange the position of B3:C29 and N3:O29? That way, the blanks are printed, but the #N/As are used in the chart.

Reply | Quote

In the File | Page Setup dialog, you can specify how error values are printed; blank is one of the options. Does this work if you print to PDF?

Reply | Quote

Thanks Hans,
The Page Setup suggestion worked perfect for printing, to pdf or any printer. Beautiful!! I forgot that option existed.

To respond to your previous suggestion, there is a formula in cell B29 that adjusts the pasted value in cell N29. That adjusted value needs to be reflected on the chart.

I still would love to find a way to make the #N/A values invisible, it would be useful in other files. Oh well.

Mahalo,
JohnJ

Reply | Quote

Use Conditional Formatting:
Select C3, Format | Conditional Formatting, Formula Is, =ISNA(C3), Press Format, Font Color White
Now copy the formatting to the other cells with Paste Special, Formats
HTH –Sam

Reply | Quote

Slammin’ Sammy! That’s IT! I couldn’t find the right formula in my feeble efforts.

btw, one can select the entire range C3:C29 at once and follow the conditional formatting instructions in your post. As long as the cell reference is relative (no $ signs) it works on all selected cells in one step.

Mahalo all,
JohnJ

Reply | Quote