Hello p45cal,
I really, really appreciate your reply.
I had found a post on Ozgrid that was most likely another solution:
https://www.ozgrid.com/forum/forum/h...ss-than-checks
But I was getting results that did not appear to be quite correct.
I also found problems when further researching the SUMPRODUCT formula:
- the formula needed to be on the same BARRIER_RCS worksheet and not the Merged Sheets worksheet.
- that column lengths for the ranges needed to be the same
Here is the formula I modified based on the formula in the attached worksheet from the Ozgrid site.
Code:
=IF(SUMPRODUCT(($B$2:$B$1015=$B145)*($C145>=CURR_LANE_WIDTHS!$B$2:$B$1015)*($C145<=CURR_LANE_WIDTHS!$C$2:$C$1015),ROW(CURR_LANE_WIDTHS!$D$2:$D$1015))=0,"",INDIRECT("CURR_LANE_WIDTHS!D"&SUMPRODUCT(($B$2:$B$1015=$B145)*($C145>=CURR_LANE_WIDTHS!$B$2:$B$1015)*($C145<=CURR_LANE_WIDTHS!$C$2:$C$1015),ROW(CURR_LANE_WIDTHS!$D$2:$D$1015))))
For copying the formula across I needed to change the column ID in the formula and the column number in the INDIRECT("CURR_LANE_WIDTHS!
D"&SUMPRODUCT part as well.
I also played with the array formula posted by mikerickson but could not get it to work.
I must admit when I looked at the formula from the Ozgrid post I did not expect a reply to this post, so I really do appreciate your reply.
Regards,
Dave T