Oct 14 2019 11:33 AM
Hello,
I'm looking for help in rewriting this formula so that it increases by 10 rows at a time. I haven't been able to figure out the correct sequence using OFFSET, but any solution would be much appreciated.
=IFERROR(LOOKUP(2,1/(ISNUMBER('PATIENT TRACKING'!R4:R13)),'PATIENT TRACKING'!R4:R13),"NO SCORE")
When I copy down I would like the second cell to reference R14:23 then the third to reference R24:33 and so on.
Thank you
Oct 14 2019 02:05 PM
Let say your first formula is in row #3. When
OFFSET($R$4,(ROW()-ROW($A$3))*10,0,10)
returns R4:R13 range. If drag it down on one cell, next returned range will be R14:R23.
Oct 15 2019 09:19 AM
@Sergei Baklan thank you for the response.
How would I correctly incorporate this into the formula I posted?
Thanks,
Daniel
Oct 15 2019 02:50 PM
Daniel, that could be like
=IFERROR(LOOKUP(2,1/(ISNUMBER(OFFSET('PATIENT TRACKING'!$R$4,(ROW()-ROW($A$3))*10,0,10))),OFFSET('PATIENT TRACKING'!$R$4,(ROW()-ROW($A$3))*10,0,10)),"NO SCORE")
if you first formula is within row 3 (e.g in B3).
Oct 15 2019 10:13 PM
Instead of the volatile OFFSET, a non-volatile alternative using INDEX would be:
=IFERROR(LOOKUP(2,1/(ISNUMBER(INDEX('PATIENT TRACKING'!R:R,4+(ROWS($1:1)-1)*10):INDEX('PATIENT TRACKING'!R:R,13+(ROWS($1:1)-1)*10))),
INDEX('PATIENT TRACKING'!R:R,4+(ROWS($1:1)-1)*10):INDEX('PATIENT TRACKING'!R:R,13+(ROWS($1:1)-1)*10)),
"NO SCORE")