# Drag excel formular and auto increment a number

Hi all,

I am tring to copy the below code down in my exel spread sheet so it does the following
=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),1)
=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),2)
=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),3)
=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),4)
=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),5)
------------------------------------------------------------------------------
=LARGE(IF(Result!\$N\$2:\$N\$1000=8,Result!\$O\$2:\$O\$1000,""),1)
=LARGE(IF(Result!\$N\$2:\$N\$1000=8,Result!\$O\$2:\$O\$1000,""),2)
=LARGE(IF(Result!\$N\$2:\$N\$1000=8,Result!\$O\$2:\$O\$1000,""),3)
=LARGE(IF(Result!\$N\$2:\$N\$1000=8,Result!\$O\$2:\$O\$1000,""),4)
=LARGE(IF(Result!\$N\$2:\$N\$1000=8,Result!\$O\$2:\$O\$1000,""),5)
------------------------------------------------------------------------------
(--- indicates a empty row)

I have tried to drap and drop it down so the number after Result!\$N\$2:\$N\$1000= increases each time but it just loops round!!!! any ideas how i can just copy it down???
###### Who is Participating?

x

Commented:
I would use Excel formulas to write those formulas, copy-paste the result out to notepad, and finally copy-paste back into a new column in Excel.

Round-tripping through notepad extracts the resultant text only on the way out and interprets as a formula on the way in.

See attachment for use and below code block for main concept...
``````=IF(A1<>"","=LARGE(IF(Result!\$N\$2:\$N\$1000="&A1&",Result!\$O\$2:\$O\$1000,""""),"&B1&")","")
``````
ComplexFormulaPaste.xlsx
0

Commented:
A sample worksheet would be helpful :)
0

Commented:
Basically,
``````=MOD(ROW(),6)
``````
will do the job. It will produce the numbers 1 to 5 in rows 1:5, a zero in row 6 (which is blank in your worksheet) and resume with 1 thru 5 in row 7 etc.
If your first formula isn't in row 1 you should reduce the result returned by ROW() by a number equal to the number of rows above the first formula. For example,
``````=MOD(ROW()-2,6)
``````
would be the formula if the first of your formulas is in row 3.
Here is your formula with the above idea incorporated:-
``````=LARGE(IF(Result!\$N\$2:\$N\$1000=7,Result!\$O\$2:\$O\$1000,""),MOD(ROW()-2,6))
``````
0

Commented:
BTW, you could use a similar system to specify the result you are looking for - the 7 in
Result!\$N\$2:\$N\$1000=7.
This formula will count from 0 upward each 6 rows.
``````=INT(ROW()/6)
``````
Again, you can adjust the result to start counting at a row other than 1 in the manner already described. Similarly, if you need to start counting with 7 this formula would do the job, =7+INT(ROW()/6). Note that this method would return the wrong result for each row number divisible by 6. That can be compensated by enlarging the formula a little, but in your case that is not necessary because precisely those rows are blank.
Here is your formula with the above thought incorporated:-
``````=LARGE(IF(Result!\$N\$2:\$N\$1000=INT((ROW()-2)/6),Result!\$O\$2:\$O\$1000,""),MOD(ROW()-2,6))
``````
0

Older than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.