Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dynamic named range | New Users to Excel | |||
Dynamic Named Range | Excel Discussion (Misc queries) | |||
Dynamic Named Range | Excel Discussion (Misc queries) | |||
Can't chart dynamic named range?? | Charts and Charting in Excel | |||
Dynamic Named Range | Charts and Charting in Excel |