Hi Domenic & Biff,
I've implemented the formula below across the first two columns ("B" and
"C") in Sheet2 but it produces the N/A error on some rows and
unfortunately, still only returns the same 4 consecutive values that the
previous formula returned. I should see 9 values in columns "B" and "C"
that are consecutive.
Column "A" is empty and remembered to enter with CONTROL+SHIFT+ENTER.
Any further ideas and help much appreciated.
Thanks
Sam
-----------------------------------------------------------------------
Here's my second attempt at a solution. This time I hope I've covered
all the bases. I guess we'll see... :)
Sheet2!B19, copied across and down:
=IF(INDEX(Sheet1!$B19:$G19,MATCH(2,1/((Sheet1!$C19:$H19-Sheet1!$B19:$G19=
1)*(Sheet1!$B19:$G19=80)*(Sheet1!$C19:$H19<=89))) )=B$1,SUM(B$1,A19),IF(C
OUNTIF($A19:A19,"0")=1,SUM(A19,1),""))
....confirmed with CONTROL+SHIFT+ENTER. Note that the same caution
applies here. Column A, starting at A19, must either be blank or
contain text values.
--
Message posted via
http://www.officekb.com