I am trying to creat a list of names and a "Yes" value on one sheet from a
larger list on another sheet where the list I am creating includes only the
names scoring a certain value, in this case, a 0 (the zero value is in column
P).
SHEET 1
Column A Column B . . . Column N . . . Column P
Last Name First Name (Value 0, 5, or 10) (Value 0-100)

Needed on Sheet 2, 3 columns
Last Name - First Name - Yes or "

Hope this makes sense!! On sheet one Last name in column A, First Name in
column B, and if value in column N is 0, value needs to = "Yes" on the
second sheet. I can't figure out how to write a formula on the second sheet
that will find a name associated with the 0 value on the first sheet, enter
the values to make teh list on the second sheet, then search the list for the
next one until the list is done. Thanks very much for any help on this!!

One way to set it up ...

Assuming source data is in Sheet1, cols A to P,
data from row2 down. Col P = key col

In Sheet2,

Put in A2:
=IF(Sheet1!P2="","",IF(Sheet1!P2=0,ROW(),""))
Leave A1 blank

Put in B2:
=IF(ROW(A1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL ($A:$A,ROW(A1))))
Copy B2 to C2

Put in D2:
=IF(ROW(A1)COUNT($A:$A),"",IF(INDEX(Sheet1!N:N,SM ALL($A:$A,ROW(A1)))0,"Yes",""))

Select A2:D2, copy down to cover the max expected extent of data in Sheet1,
eg copy down to say, D200. Hide away col A. Cols B to D will return the
required results, ie the list of last / first names with zero values in col P
in Sheet1, with all results neatly bunched at the top, and with a "Yes"
indicated where col N in Sheet1 is 0.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Wayne4js" wrote:
I am trying to creat a list of names and a "Yes" value on one sheet from a
larger list on another sheet where the list I am creating includes only the
names scoring a certain value, in this case, a 0 (the zero value is in column
P).
SHEET 1
Column A Column B . . . Column N . . . Column P
Last Name First Name (Value 0, 5, or 10) (Value 0-100)

Needed on Sheet 2, 3 columns
Last Name - First Name - Yes or "

Hope this makes sense!! On sheet one Last name in column A, First Name in
column B, and if value in column N is 0, value needs to = "Yes" on the
second sheet. I can't figure out how to write a formula on the second sheet
that will find a name associated with the 0 value on the first sheet, enter
the values to make teh list on the second sheet, then search the list for the
next one until the list is done. Thanks very much for any help on this!!