Question on Named range with formula


D

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
 
Ad

Advertisements

D

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.
 
Ad

Advertisements

D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top