Spreadsheet: 525356d1498676719-output-a-number-based-on-depth-and-date-when-there-are-overlapping-dates-and-depths-bhaslideexample
Sheet: Slide Sheet

 |  |  |  |  |  |  |  |  |  |  | curent output(formula) |  | Desired output (manually entered)
 | 0 |  |  | depth in | depth out | BHA #
 | 114.02 |  |  | 109 | 723 | 1 |  |  |  |  |  |  | 1
 | 123.37 |  |  | 723 | 2390 | 2
 | 132.69 |  |  | 878 | 1668 | 3
 | 141.86 |  |  | 1668 | 2111 | 4
 | 151.32 |  |  | 816 | 2161 | 5
 | 160.92 |  |  | 778 | 2455 | 6
 | 170.27
 | 179.59
 | 188.83
 | 198.13 |  |  | *Actual cells (Chart) are AN12:AN50
 | 207.81 |  |  | in reality I sometimes have multiple slide sheets
 | 217.15 |  |  | in the example here there would be a total of 4 slide sheets
 | 226.54 |  |  | when the new one starts (in this case) it would be with BHA#3
 | 236 |  |  | so this is where the problems arise, not only on this original sheet
 | 245.39 |  |  | but on the additional ones as well
 | 254.81 |  |  | the original slide sheet is names slide sheet as on tab below
 | 264.28 |  |  | additional slide sheets are names sidetrack (1), (2) etc
 | 273.71 |  |  | but I don't think this is necessarily relevant
 | 283.06
 | 292.39
 | 301.64
 | 311.07
 | 320.4
 | 329.75
 | 339.2
 | 348.67
 | 357.93
 | 367.1
 | 376.42
 | 386
 | 395.18
 | 404.52
 | 413.77
 | 423.19
 | 432.42
 | 441.77
 | 451.11
 | 460.42
 | 469.79
 | 479.12
 | 488.66
 | 497.99
 | 507.3099999999999
 | 516.63
 | 525.95
 | 535.32
 | 544.46
 | 553.78
 | 563.18
 | 572.17
 | 581.52
 | 590.83
 | 603.72
 | 616.62
 | 629.48
 | 642.36
 | 655.23
 | 668.09
 | 680.98
 | 693.93
 | 706
 | 729 |  |  |  |  |  |  |  |  |  |  |  | 2
 | 738.55
 | 751.44
 | 764.35
 | 777.24
 | 790.06
 | 808.14
 | 821.07
 | 833.96
 | 846.87
 | 859.79
 | 872.65
 | 885.52
 | 898.4
 | 911.27
 | 924.14
 | 937.03
 | 946.3
 | 955.67
 | 965.01
 | 974.21
 | 983.58
 | 992.92
 | 1002.22
 | 1011.53
 | 1020.85
 | 1030.2
 | 1039.52
 | 1048.69
 | 1058.15
 | 1067.75
 | 1077.1
 | 1086.42
 | 1095.66
 | 1104.96
 | 1114.64
 | 1123.98
 | 1133.37
 | 1142.83
 | 1152.22
 | 1161.64
 | 1171.11
 | 1180.54
 | 1189.89
 | 1199.22
 | 1208.47
 | 1217.9
 | 1227.23
 | 1236.58
 | 1246.03
 | 1255.5
 | 1264.76
 | 1273.93
 | 1283.25
 | 1292.83
 | 1302.01
 | 1311.35
 | 1320.6
 | 1330.02
 | 1339.25
 | 1348.6
 | 1357.94
 | 1367.25
 | 1376.62
 | 1385.95
 | 1395.49
 | 1404.82
 | 1414.14
 | 1423.46
 | 1432.78
 | 1442.15
 | 1451.29
 | 1460.61
 | 1470.01
 | 1479
 | 1488.35
 | 1497.66
 | 1510.57
 | 1523.45
 | 1536.35
 | 1549.17
 | 1562.04
 | 1574.91
 | 1587
 | 1599.75
 | 1609.09
 | 1618.29
 | 1627.66
 | 1637
 | 1646.3
 | 1655.61
 | 1664.93
 | 1674.28
 | 1683.6
 | 1692.77
 | 1702.23
 | 1711.83
 | 1721.18
 | 1730.5
 | 1739.74
 | 1749.04
 | 1758.72
 | 1768.06
 | 1777.45
 | 1786.91
 | 1796.3
 | 1805.72
 | 1815.19
 | 1824.62
 | 1833.97
 | 1843.3
 | 1852.55
 | 1861.98
 | 1871.31
 | 1880.66
 | 1890.11
 | 1899.58
 | 1908.84
 | 1918.01
 | 1927.33
 | 1936.91
 | 1946.09
 | 1955.43
 | 1964.68
 | 1974.1
 | 1983.33
 | 1992.68
 | 2002.02
 | 2011.33
 | 2020.7
 | 2030.03
 | 2039.57
 | 2048.9
 | 2058.22
 | 2067.54
 | 2076.86
 | 2086.23
 | 2095.37
 | 2104.69
 | 2114.09
 | 2123.08
 | 2132.43
 | 2141.74
 | 2154.62
 | 2167.5
 | 2180.36
 | 2193.25
 | 2206.11
 | 2218.98
 | 2231.87
 | 2244.75
 | 2257.62
 | 2270.49
 | 2283.37
 | 2296.6
 | 2309.48
 | 2322.29
 | 2335.15
 | 2348.42
 | 2361.31
 | 2373