[DocBrown]

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]

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

[DocBrown]

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