dynamic named range
 

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.

dynamic named range
 

You would need to use VBA and change the range every time a cell changes as
far as I can see.

That OK?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" wrote in message
...
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.

dynamic named range
 

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

dynamic named range
 

Fantastic! Thank you so much!!

"Debra Dalgleish" wrote in message
...
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