"FIND" EXCEL FORMULA

Posted by Gordon on June 23, 2001 4:29 PM

I need a formula for excel '97.
In worksheet1, I have column A as "Totals" and column B as "Codes". "Totals" is a calculation of a data entry worksheet. I need to look at column A "Totals" to find values greather than 0. If the value is greater than 0, I need to pull that value along with the corresponding code from column B into my new worksheet. Does anyone have a formula that will do this? Please???
Thanks!

Posted by Damien on June 23, 2001 9:19 PM

ok put this formula into your worksheet cells where you want the data I have assumed that coloum a is dollar values and coloum b the code this formula combines the value and code together
I think that is what you are after now what you need to do is put the name of the work sheet that you are getting the data form in front of the referrences e.g if your data worksheet was called sheet1
=if(sheet1!A2>0,Sheet1!A2&Sheet1!B2,"")

=IF(A2>0,A2&B2,"")

if you don't want to combine the value and the code just don't put the & b2 in good luck you'll get there

Posted by Aladin Akyurek on June 23, 2001 11:17 PM

Copying recs where Totals>0 to another sheet without blank rows

Gordon

I'll assume your data to be on Sheet1 in A from A1 on (the label "Totals" in A1) and in B from B1 on (the label "Codes" in B1).

In C2 array-enter: =IF(A2>0,RANK(A2,A$2:A$6)+COUNTIF(A$2:A2,A2)-1,"")

In order to array-enter a formula, hit CONTROL+SHIFT+ENTER at the same time (not just ENTER).
Copy the formula in C2 as far as needed.

In D2 enter: =MAX(C2:C6)

Name D2 "NumRecs" via the Name Box.

On Sheet2

inn A1 enter: Totals
in B1 enter: Codes
in A2 enter: =IF(ROW()-1&LT;=NumRecs,INDEX(Sheet1!A$2:A$16,MATCH(ROW()-1,Sheet1!$C$2:$C$16,0)),"")

Copy this to B2 and then down as far as needed.

There will be no blank rows on Sheet2. If it is important, you can sort these records by Codes.

Aladin

============

Posted by Aladin Akyurek on June 25, 2001 6:53 AM

Re: Copying recs where Totals>0 to another sheet without blank rows

Gordon -- The formula in C2 needs not to be entered as an array-formula, a fact that makes the system more attractive.

Aladin

============