Solved

Drag excel formular and auto increment a number

Posted on 2013-12-13
6
478 Views
Last Modified: 2014-01-25
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???
0
Comment
Question by:runnerjp2005
6 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39717113
A sample worksheet would be helpful :)
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 250 total points
ID: 39717131
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
 
LVL 14

Assisted Solution

by:Faustulus
Faustulus earned 250 total points
ID: 39721095
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 39721110
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 39808433
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now