Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Formatting (Excel 2002)

    I'm using a VLOOKUP function. When I initially open the worksheet, the cells have the "#N/A" error message until I enter a value in the target cell. How can I hide the "#N/A" in the cells?

    Thanks,
    Craig.

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

    Re: Conditional Formatting (Excel 2002)

    One possibility is to use the ISNA function in the formulas:

    =IF(ISNA(VLOOKUP(...),"",VLOOKUP(...))

    If you want to use conditional formatting, select the cells, then select Format | Conditional Formatting...
    Under Condition1, select Formula Is.
    In the box next to it, enter this formula:
    =ISNA(A1)
    where A1 is the address of the active cell within the selected range. (If you want to hide other errors as well, use ISERROR instead of ISNA)
    Click Format..., and select white (or rather, the background color of the worksheet) in the Color dropdown in the Font tab.
    Click OK twice.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Conditional Formatting (Excel 2002)

    You can also conditional format the NA cells to have the same font color as their cell background. Select the column, and, assuming it starts in A1, Format, Conditional format, formula is =ISNA(A1), format the font color to the cell background (usually white), OK out, copy the format down. The NA's are still there but they don't print.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Conditional Formatting (Excel 2002)

    The non-printing will only work, black on black or white on white on most printers. Colored text on colored backgrounds will be hidden on display, but most printers will print text in black no matter what its color

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Conditional Formatting (Excel 2002)

    You are right.. I have to remember that my setup is not universal. I should also have pointed out that the cell text is also visible if the cell is highlighted by being part of a selection, and probably in some other situations.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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