Help with formula

Copper Contributor

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

4 Replies

@Registryuser 

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.

@Sergei Baklan thank you for the response.

 

How would I correctly incorporate this into the formula I posted?

 

Thanks,

 

Daniel

@Registryuser 

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

@Registryuser 

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")