 Highlighted

# Help with formula

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
Highlighted

# Re: Help with formula

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.

Highlighted

# Re: Help with formula

@Sergei Baklan thank you for the response.

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

Thanks,

Daniel

Highlighted

# Re: Help with formula

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

Highlighted

# Re: Help with formula

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