I have a worksheet with many named ranges. I have questions on when the named
range is accessible.

I have some ranges where the Refers to is defined like:
Name - ListOne:
=Lists!$V$7:$V$20

Others use dynamic lists where the Refers to is defined like:
Name - ListTwo
=OFFSET(Lists!$G$6,0,0,COUNTA(Lists!$G$6:$G$39),1)

I can use the ListOne in cell validation and cell formulas. And I can use
ListTwo in cell validation. BUT I can't seem to use ListTwo in formulas. The
name ListTwo does not appear in the cell reference box in the formula bar,
but the ListOne does.

The question is: how do I use named ranges of the form of ListTwo in cell
formulas? Or is that possible?

Thanks,
John

Those dynamic names don't show up in the name box.

But you can type them into your formula and use them ok.

Or you can start your formula, like:
=sum(
and hit F3 to show the Paste Names dialog

They'll show up there.

DocBrown wrote:

I have a worksheet with many named ranges. I have questions on when the named
range is accessible.

I have some ranges where the Refers to is defined like:
Name - ListOne:
=Lists!$V$7:$V$20

Others use dynamic lists where the Refers to is defined like:
Name - ListTwo
=OFFSET(Lists!$G$6,0,0,COUNTA(Lists!$G$6:$G$39),1)

I can use the ListOne in cell validation and cell formulas. And I can use
ListTwo in cell validation. BUT I can't seem to use ListTwo in formulas. The
name ListTwo does not appear in the cell reference box in the formula bar,
but the ListOne does.

The question is: how do I use named ranges of the form of ListTwo in cell
formulas? Or is that possible?

Thanks,
John

--

Dave Peterson

Thanks! That pointed me in a new direction.

Turns out there was a different problem with the formula. I was trying to
use the named list as a two column lookup table and the last parm of the
OFFSET function needed to be 2 instead of 1.

So I CAN use that ListTwo in a formula and lookup table. Yea!

Thanks again,
John

"Dave Peterson" wrote:

Those dynamic names don't show up in the name box.

But you can type them into your formula and use them ok.

Or you can start your formula, like:
=sum(
and hit F3 to show the Paste Names dialog

They'll show up there.

DocBrown wrote:

I have a worksheet with many named ranges. I have questions on when the named
range is accessible.

I have some ranges where the Refers to is defined like:
Name - ListOne:
=Lists!$V$7:$V$20

Others use dynamic lists where the Refers to is defined like:
Name - ListTwo
=OFFSET(Lists!$G$6,0,0,COUNTA(Lists!$G$6:$G$39),1)

I can use the ListOne in cell validation and cell formulas. And I can use
ListTwo in cell validation. BUT I can't seem to use ListTwo in formulas. The
name ListTwo does not appear in the cell reference box in the formula bar,
but the ListOne does.

The question is: how do I use named ranges of the form of ListTwo in cell
formulas? Or is that possible?

Thanks,
John

--

Dave Peterson