Create Named List (range) based on check list

[Robert Hatcher]

Column A is a list. Column B has “x” in some cells. I need to create a
named range, using a formula, that is populated with items from Column
A when there is a corresponding “x” in column B.
I cannot use a macro in this workbook.
I’ll be grateful for any help
Robert

 

[GS]

Robert Hatcher submitted this idea :

Column A is a list. Column B has “x†in some cells. I need to create a
named range, using a formula, that is populated with items from Column
A when there is a corresponding “x†in column B.
I cannot use a macro in this workbook.
I’ll be grateful for any help
Robert

Good luck!

You'd be further ahead to define a dynamic range to be populated with
the list using one of the lookup formulas. In this case, though, it
would be easier if the list was in ColB and the 'flag' was in ColA. In
any event, you'll probably end up using an array formula IMO.

 

[Don Guillett]

Column A is a list. Column B has “x” in some cells. I need to create a
named range, using a formula, that is populated with items from Column
A when there is a corresponding “x” in column B.
I cannot use a macro in this workbook.
I’ll be grateful for any help
Robert

insert>name>define>name it rangex or whatever. In the refers to box
type
to use a1:e & the row in col b with x. Look at the OFFSET function.
=OFFSET(Sheet12!$A$1,0,0,MATCH("x",Sheet12!$B:$B,0),5)

 

[Robert Hatcher]

insert>name>define>name it rangex or whatever. In the refers to box
type
to use a1:e & the row in col b with x. Look at the OFFSET function.
=OFFSET(Sheet12!$A$1,0,0,MATCH("x",Sheet12!$B:$B,0),5)

Thanks Don, I will work with that. Garry, yes, Ill make it dynamic
range, but Ill wait until I have it working first. I find that If I
try starting with a dynamic range at first it gets a bit unwieldy if
I'm trying to work out bugs.
Thanks
Robert

 

[GS]

Robert Hatcher expressed precisely :

Thanks Don, I will work with that. Garry, yes, Ill make it dynamic
range, but Ill wait until I have it working first. I find that If I
try starting with a dynamic range at first it gets a bit unwieldy if
I'm trying to work out bugs.
Thanks
Robert

Yeah, I get that! Best wishes...