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???
runnerjp2005Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Angelp1ayConnect With a Mentor 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&")","")

Open in new window

ComplexFormulaPaste.xlsx
0
 
Patrick MatthewsCommented:
A sample worksheet would be helpful :)
0
 
FaustulusConnect With a Mentor Commented:
Basically,
=MOD(ROW(),6)

Open in new window

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)

Open in new window

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

Open in new window

0
 
FaustulusCommented:
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)

Open in new window

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

Open in new window

0
 
Martin LissOlder 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
All Courses

From novice to tech pro — start learning today.