Insert a column to the left of the Include column.
In the first row of data, enter the formula:
=IF(B2="Y",MAX($A$1:A1)+1,"")
Copy down to the last row of data
In another column, in row2, (cell E2, in this example), enter the formula:
=IF(ISNA(VLOOKUP(ROW()-1,$A$2:$C$25,3,FALSE)),"",VLOOKUP(ROW()-1,$A$2:$C$25,3,FALSE))
where A2:C25 is the original list of names and the new column of numbers
Copy the formula down to the last row of data in the original table
Choose InsertNameDefine
Type a name for the range, e.g. NameList
In the refers to box, enter a formula that refers to the second list:
=OFFSET(Sheet1!$E$2,0,0,MAX(Sheet1!$A:$A),1)
Click OK
Steph wrote:
Hi. In a simple worksheet (Sheet1) there is 2 columns:
Include Name
Y Bob
Y John
N Bill
Y Tom
N Mike
How can I create a named range that will have only the Names of the people
that have a Y in the first column? And will vary when I change the contents
in the "Include" column? Thanks in advance.
--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html