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.
- 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
- 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
Bookmarks