Forum Discussion

dreiness's avatar
dreiness
Copper Contributor
Mar 05, 2020

Help rewriting formula to increase by 10 cells

Hello,

 

I'd like help rewriting this formula so it will increase the reference cells by ten rather than one when I drag down.

 

=IFERROR(LOOKUP(2,1/(ISNUMBER('PATIENT TRACKING'!I4:I13)),'PATIENT TRACKING'!I4:I13),"NO SCORE")

 

I'd like to drag down and have the next cell read

 

=IFERROR(LOOKUP(2,1/(ISNUMBER('PATIENT TRACKING'!I14:I23)),'PATIENT TRACKING'!I14:I23),"NO SCORE")

 

and so on.  I'm a relative excel novice so please use variations on this formula as much as possible rather than generics.

 

Thank you,

 

-D

excel
Formulas and Functions
office 365

5 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 05, 2020

    dreiness 

    As variant

    =IFERROR(
   LOOKUP(
      2,
      1/ISNUMBER(
         INDEX('PATIENT TRACKING'!$I:$I,4+10*(ROW()-ROW($A$1))):
         INDEX('PATIENT TRACKING'!$I:$I,13+10*(ROW()-ROW($A$1)))
        ),
      INDEX('PATIENT TRACKING'!$I:$I,4+10*(ROW()-ROW($A$1))):
      INDEX('PATIENT TRACKING'!$I:$I,13+10*(ROW()-ROW($A$1)))
   ),
"NO SCORE")

    if start from row #1

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Mar 05, 2020

    Hello dreiness,

     

    Starting in the first cell, that could be like:

    =IFERROR(LOOKUP(2,1/(ISNUMBER(INDIRECT("'PATIENT TRACKING'!I4:I"&10*ROW(A1)+3))),INDIRECT("'PATIENT TRACKING'!I4:I"&10*ROW(A1)+3)),"NO SCORE")

     

    • dreiness's avatar
      dreiness
      Copper Contributor
      Mar 06, 2020
      Unfortunately this did not work, but thank you.

Resources