Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How do you trap the #N/A (returned by vlookup) in CF? I tried =ISERROR amongst others without success.

    Also I need to format J3 based on if O7 = 0 or #N/A.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    > How do you trap the #N/A (returned by vlookup) in CF? I tried =ISERROR amongst others without success.

    Select Formula Is from the first dropdown in the Conditional Formatting dialog, then enter a formula in the box next to it:

    =ISNA(A1)

    or

    =ISERROR(A1)

    where A1 is the cell you're trying to format conditionally.

    > Also I need to format J3 based on if O7 = 0 or #N/A.

    Select J3
    Select Format | Conditional Formatting...
    Select Formula Is from the first dropdown.
    Enter the following formula in the box next to it:

    =IF(ISNA(O7),TRUE,O7=0)

    Click Format... etc.

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ISERROR should work.

    The formatting formula would be:
    =OR(O7=0,ISERROR(O7))

    You could also limit the error check to #N/A by using the more selective ISNA function. ISERROR traps all errors, such as #VALUE or #NAME?, while ISNA only traps #N/A.

  4. #4

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If O7 contains an error, O7=7 doesn't return FALSE but an error, so =OR(O7=7,ISERROR(O7)) returns an error too, instead of TRUE. So you should test in two steps, as in my reply.

Posting Permissions

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