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

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.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Nov 8, 2:37*pm, Robert Hatcher <rhatcher...@gmail.com> wrote:
> 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)

On Nov 8, 5:30*pm, Don Guillett <dguille...@gmail.com> wrote:
> On Nov 8, 2:37*pm, Robert Hatcher <rhatcher...@gmail.com> wrote:
>
> > 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)

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

Robert Hatcher expressed precisely :
> On Nov 8, 5:30Â*pm, Don Guillett <dguille...@gmail.com> wrote:
>> On Nov 8, 2:37Â*pm, Robert Hatcher <rhatcher...@gmail.com> wrote:
>>
>>> 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)
>
> 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...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc