Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Hiding #N/A (E2000)

    Happy New Year to all

    I have a cell that until other information is entered in other cells shows #N/A, when the other cells are completed a value is calculated and then shows in this cell, it all works fine but I would like to hide the #N/A. The Excel help says that a green triangle will show in the corner of the cell if there is an error in the formula and by formating it it can be hidden, however I have no green triangle because there is not a problem with the actual formula.

    Any advice please

    Cheers

    Stephen

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

    Re: Hiding #N/A (E2000)

    The general idea is to use IF and ISNA to test if the formula results in #N/A. You can replace
    <code>
    =MYFORMULA
    </code>
    with
    <code>
    =IF(ISNA(MYFORMULA),"",MYFORMULA)
    </code>
    Here is a specific example: your cell contains the formula
    <code>
    =VLOOKUP(A1,B1:C10,2,FALSE)
    </code>
    To avoid the #N/A error value if A1 is not found in B1:B10, change this to
    <code>
    =IF(ISNA(VLOOKUP(A1,B1:C10,2,FALSE)),"",VLOOKUP(A1 ,B1:C10,2,FALSE))
    </code>
    You can also use the more general ISERROR instead of ISNA.

  3. #3
    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: Hiding #N/A (E2000)

    You can use conditional formatting to set the text color to match the background color
    If the cell is A1 you could use a formula is:
    <pre>=isna(a1)</pre>

    [Modify as needed]

    Then set the format of the text color to match the background

    You could check the other information is available (if you are wainting for cells A1-C1), something like:
    <pre>=if(or(a1="",B1="",C1=""),"",[your current formula])</pre>


    Steve

  4. #4
    2 Star Lounger
    Join Date
    Sep 2003
    Location
    London, Gtr London, United Kingdom
    Posts
    153
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Hiding #N/A (E2000)

    Thanks Hans and Steve for the prompt response

    Stephen

Posting Permissions

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