Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    make error values disappear (2003 SP2)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make error values disappear (2003 SP2)

    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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: make error values disappear (2003 SP2)

    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?

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: make error values disappear (2003 SP2)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: make error values disappear (2003 SP2)

    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

  6. #6
    Star Lounger
    Join Date
    Sep 2002
    Location
    Honolulu, Hawaii, USA
    Posts
    63
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: make error values disappear (2003 SP2)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •