+ Reply to Thread
Results 1 to 2 of 2

If(iserror function not functioning, but Iserror, and formula stand-alone work fine.

  1. #1
    Registered User
    Join Date
    12-06-2011
    Location
    Windsor, Ontario
    MS-Off Ver
    Excel 2003
    Posts
    4

    If(iserror function not functioning, but Iserror, and formula stand-alone work fine.

    Here is the formula I am using:

    =IF(ISERROR(INDEX(THD!A:A,SMALL(IF(Date>=$A$7, IF(Date<=$B$7, IF(THDpanel=$B$3,IF(Vertical_Depth<=3,ROW(Vertical_Depth)))))))),ROW(1:1)))), "", INDEX(THD!A:A,SMALL(IF(Date>=$A$7, IF(Date<=$B$7, IF(THDpanel=$B$3,IF(Vertical_Depth<=3,ROW(Vertical_Depth))))),ROW(1:1))))

    Excel won't accept the function (gives a "formula you have typed contains an error and highlights the ROW(vertical_depth) portion of the formula). But when I enter the fragments (both the logical_test (1) and the value_if_false (2) formulas in their own cells they return values and work properly.
    1. ISERROR(INDEX(THD!A:A,SMALL(IF(Date>=$A$7, IF(Date<=$B$7, IF(THDpanel=$B$3,IF(Vertical_Depth<=3,ROW(Vertical_Depth)))))))),ROW(1:1)))3
    2. INDEX(THD!A:A,SMALL(IF(Date>=$A$7, IF(Date<=$B$7, IF(THDpanel=$B$3,IF(Vertical_Depth<=3,ROW(Vertical_Depth)))))))),ROW(1:1))

    *Note: Date, THDpanel, Vertical_Depth are all named ranges within the sheet and THD! is a separate worksheet in the workbook

    *Note: The formulas when separated into 3 columns, 1 giving the value of (2), 1 determining whether it is an error (1), and 1 returning the value in the first column if it is not an error and a blank if it is, achieve the desired result, but in theory it should be accomplished by the 1 catch-all formula originally entered.

    Any feedback as to possible reasons for this would be greatly appreciated.

    Thank you,

    Cadojib
    Last edited by cadojib; 12-13-2011 at 05:52 PM. Reason: Formatting

  2. #2
    Valued Forum Contributor
    Join Date
    02-09-2006
    Location
    Melbourne, Australia
    MS-Off Ver
    Office 2016
    Posts
    1,077

    Re: If(iserror function not functioning, but Iserror, and formula stand-alone work fi

    I suggest you upload a copy of your workbook so we can see what the problem might be. Remember to replace any confidential or sensitive data before uploading.
    Trish in Oz
    -------------
    A problem well defined is a puzzle half solved


    If you attach a sample of your workbook it will be easier to find a solution. Remember to remove/replace sensitive data before uploading the file. Look here if you need help with attachments:
    http://www.excelforum.com/faq.php?fa...b3_attachments

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1