Named Ranges don't show up in drop-down list

Hi, I'm trying to make a chart that depends on dynamic named ranges. I've got the dynamic ranges figured out (I think) -- but I'm having trouble getting the Names recognized. I can create them but they don't show up in the Names Drop-Down list. As a consequence, the names aren't recognized in chart source data references. Help!

Here's what happens.

1) I use the Insert > Name > Define tool to create my dynamic ranges. I think this part is OK. For example:

CensusDataLabels
=OFFSET('Census'!$B$7,COUNTIF('Census'!$B$7:$B$40,"=q*")-1,0,-16,1)

If I click inside the above formula, Excel selects the correct cells, so I think the formula is OK. The problem is the next step.

2) If I OK the Insert Name dialog and go back to my worksheet, none of my new ranges appear in the Names drop-down list. If I go back to my Insert Name dialog, all of my ranges are still there.

So I tried a different method of creating names. If I create a simple one directly in the worksheet (select cells in sheet, then click in Names box and type new Name) -- then the new Name does show up in the Names Drop-Down. So far so good. If I open the Insert> Name> Define dialog, then my new Name is there. Cool! So I tried to edit my new Name. I left the title alone and just changed the reference. All seemed well... but if I go back to my worksheet, the new Name is gone, no longer in the Drop-Down.

So- any tips as to why my names won't show up in the Names drop-down box? I've tried this in two different workbooks with the same problem and I'm stumped. Any help would be most appreciated!

should have noted- am using Excel 2003. Thanks!

See if this helps:

http://www.peltiertech.com/Excel/Cha...micLast12.html

Biff

"hds" <hds.2adrxb_1151949001.7387@excelforum-nospam.com> wrote in message
news:hds.2adrxb_1151949001.7387@excelforum-nospam.com...
>
> Hi, I'm trying to make a chart that depends on dynamic named ranges.
> I've got the dynamic ranges figured out (I think) -- but I'm having
> trouble getting the Names recognized. I can create them but they don't
> show up in the Names Drop-Down list. As a consequence, the names aren't
> recognized in chart source data references. Help!
>
> Here's what happens.
>
> 1) I use the Insert > Name > Define tool to create my dynamic ranges.
> I think this part is OK. For example:
>
> CensusDataLabels
> =OFFSET('Census'!$B$7,COUNTIF('Census'!$B$7:$B$40,"=q*")-1,0,-16,1)
>
> If I click inside the above formula, Excel selects the correct cells,
> so I think the formula is OK. The problem is the next step.
>
> 2) If I OK the Insert Name dialog and go back to my worksheet, none of
> my new ranges appear in the Names drop-down list. If I go back to my
> Insert Name dialog, all of my ranges are still there.
>
> So I tried a different method of creating names. If I create a simple
> one directly in the worksheet (select cells in sheet, then click in
> Names box and type new Name) -- then the new Name does show up in the
> Names Drop-Down. So far so good. If I open the Insert> Name> Define
> dialog, then my new Name is there. Cool! So I tried to edit my new
> Name. I left the title alone and just changed the reference. All
> seemed well... but if I go back to my worksheet, the new Name is gone,
> no longer in the Drop-Down.
>
> So- any tips as to why my names won't show up in the Names drop-down
> box? I've tried this in two different workbooks with the same problem
> and I'm stumped. Any help would be most appreciated!
>
>
> --
> hds
> ------------------------------------------------------------------------
> hds's Profile:
> http://www.excelforum.com/member.php...o&userid=36003
> View this thread: http://www.excelforum.com/showthread...hreadid=557889
>

to use a defined name range as the source

=yourworkbookname.xls!yourdefinedname

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"hds" <hds.2adrxb_1151949001.7387@excelforum-nospam.com> wrote in message
news:hds.2adrxb_1151949001.7387@excelforum-nospam.com...
>
> Hi, I'm trying to make a chart that depends on dynamic named ranges.
> I've got the dynamic ranges figured out (I think) -- but I'm having
> trouble getting the Names recognized. I can create them but they don't
> show up in the Names Drop-Down list. As a consequence, the names aren't
> recognized in chart source data references. Help!
>
> Here's what happens.
>
> 1) I use the Insert > Name > Define tool to create my dynamic ranges.
> I think this part is OK. For example:
>
> CensusDataLabels
> =OFFSET('Census'!$B$7,COUNTIF('Census'!$B$7:$B$40,"=q*")-1,0,-16,1)
>
> If I click inside the above formula, Excel selects the correct cells,
> so I think the formula is OK. The problem is the next step.
>
> 2) If I OK the Insert Name dialog and go back to my worksheet, none of
> my new ranges appear in the Names drop-down list. If I go back to my
> Insert Name dialog, all of my ranges are still there.
>
> So I tried a different method of creating names. If I create a simple
> one directly in the worksheet (select cells in sheet, then click in
> Names box and type new Name) -- then the new Name does show up in the
> Names Drop-Down. So far so good. If I open the Insert> Name> Define
> dialog, then my new Name is there. Cool! So I tried to edit my new
> Name. I left the title alone and just changed the reference. All
> seemed well... but if I go back to my worksheet, the new Name is gone,
> no longer in the Drop-Down.
>
> So- any tips as to why my names won't show up in the Names drop-down
> box? I've tried this in two different workbooks with the same problem
> and I'm stumped. Any help would be most appreciated!
>
>
> --
> hds
> ------------------------------------------------------------------------
> hds's Profile:
> http://www.excelforum.com/member.php...o&userid=36003
> View this thread: http://www.excelforum.com/showthread...hreadid=557889
>

Thanks for the replies. Still no luck. Let me restate the problem-

If I define a Name using the "OFFSET" function, the Name doesn't show up in the Names Drop-Down box. Does that make sense? Honestly- I've tried a simplistic test, and it fails.

For example- I tried:
Name1
=OFFSET('Census'!$A$1,1,1,1,1)

Even this won't show up in my DropDown box -- but if I go back to my Insert Names box, there it is. I'm sure I must be making some simple mistake but I just cannot figure it out. I've reviewed Names in my Excel reference book, and Googled plenty of documentation... but am really stumped.

Any other ideas? (really appreciate the help!)

Originally Posted by Don Guillett

to use a defined name range as the source

=yourworkbookname.xls!yourdefinedname

Thanks Don- but if the Named Range does not show up in the Names drop-down list, the charts won't recognize the Name. Comes up as an invalid reference.

what formula do u use in the datavalidation-list ?





"hds" skrev:

>
> Thanks for the replies. Still no luck. Let me restate the problem-
>
> If I define a Name using the "OFFSET" function, the Name doesn't show
> up in the Names Drop-Down box. Does that make sense? Honestly- I've
> tried a simplistic test, and it fails.
>
> For example- I tried:
> Name1
> =OFFSET('Census'!$A$1,1,1,1,1)
>
> Even this won't show up in my DropDown box -- but if I go back to my
> Insert Names box, there it is. I'm sure I must be making some simple
> mistake but I just cannot figure it out. I've reviewed Names in my
> Excel reference book, and Googled plenty of documentation... but am
> really stumped.
>
> Any other ideas? (really appreciate the help!)
>
>
> --
> hds
> ------------------------------------------------------------------------
> hds's Profile: http://www.excelforum.com/member.php...o&userid=36003
> View this thread: http://www.excelforum.com/showthread...hreadid=557889
>
>

Originally Posted by excelent

what formula do u use in the datavalidation-list ?

No datavalidation-list. I'm talking about the standard Excel Name Box that appears at the far left end of the toolbar. But thanks for trying.

FYI - I've even tried creating a new blank workbook. Even there I can't get a simple offset range to be recognized in the Name Box.

ok try take a look at mine sample maby it can give u a hint

http://pmexcelent.dk/DynamicShart.xls

Dynamic names like these have never shown up in the Name box dropdown.

But you can still define them and select them (edit|Goto and type the name and
hit enter) or by typing the name in the name box and hitting enter. (I find
that a nice way to find out if my formula represented what I really wanted,
too.)

Your name formula worked fine for me, though.

And to make working with names easier, get Jan Karel Pieterse's (with Charles
Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

hds wrote:
>
> Hi, I'm trying to make a chart that depends on dynamic named ranges.
> I've got the dynamic ranges figured out (I think) -- but I'm having
> trouble getting the Names recognized. I can create them but they don't
> show up in the Names Drop-Down list. As a consequence, the names aren't
> recognized in chart source data references. Help!
>
> Here's what happens.
>
> 1) I use the Insert > Name > Define tool to create my dynamic ranges.
> I think this part is OK. For example:
>
> CensusDataLabels
> =OFFSET('Census'!$B$7,COUNTIF('Census'!$B$7:$B$40,"=q*")-1,0,-16,1)
>
> If I click inside the above formula, Excel selects the correct cells,
> so I think the formula is OK. The problem is the next step.
>
> 2) If I OK the Insert Name dialog and go back to my worksheet, none of
> my new ranges appear in the Names drop-down list. If I go back to my
> Insert Name dialog, all of my ranges are still there.
>
> So I tried a different method of creating names. If I create a simple
> one directly in the worksheet (select cells in sheet, then click in
> Names box and type new Name) -- then the new Name does show up in the
> Names Drop-Down. So far so good. If I open the Insert> Name> Define
> dialog, then my new Name is there. Cool! So I tried to edit my new
> Name. I left the title alone and just changed the reference. All
> seemed well... but if I go back to my worksheet, the new Name is gone,
> no longer in the Drop-Down.
>
> So- any tips as to why my names won't show up in the Names drop-down
> box? I've tried this in two different workbooks with the same problem
> and I'm stumped. Any help would be most appreciated!
>
> --
> hds
> ------------------------------------------------------------------------
> hds's Profile: http://www.excelforum.com/member.php...o&userid=36003
> View this thread: http://www.excelforum.com/showthread...hreadid=557889

--

Dave Peterson

Originally Posted by excelent

ok try take a look at mine sample maby it can give u a hint

http://pmexcelent.dk/DynamicShart.xls

THANK YOU! It seems to be working for me now in my own charts. Here's what I'm seeing:

1) the behavior I've described in this thread seems to be correct. In other words- the Insert>Name box shows all names, but the same names do not show up in the Name Box on the toolbar when the Names refer to formulas (at least- not when referring to Offset formula).

2) In spite of my earlier post- Charts WILL recognize names that do not show up in the Name Box. I must have made a typo in my earlier attempts. Your chart convinced me to try again.

Thanks so much for the help, I really appreciate it! (And really happy my chart now seems to be working )

Originally Posted by Dave Peterson

Dynamic names like these have never shown up in the Name box dropdown.

But you can still define them and select them (edit|Goto and type the name and
hit enter) or by typing the name in the name box and hitting enter. (I find
that a nice way to find out if my formula represented what I really wanted,
too.)

Your name formula worked fine for me, though.

And to make working with names easier, get Jan Karel Pieterse's (with Charles
Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

<snip>
--

Dave Peterson

Thanks Dave- finally figured that out! As noted above- I must have had a typo in the Chart Source Data reference. NameManager looks like just the tool for me. Looking forward to trying it out.

Just out of curiosity- is this naming behavior documented somewhere? I'm usually a darn good researcher, and was really frustrated not to be able to find documentation about this.

Thanks again, really appreciate the response!!

I've seen it documented in the newsgroups <bg>, but I don't recall seeing it in
Excel's help--but to be honest, I haven't looked too hard.

hds wrote:
>
> Dave Peterson Wrote:
> > Dynamic names like these have never shown up in the Name box dropdown.
> >
> > But you can still define them and select them (edit|Goto and type the
> > name and
> > hit enter) or by typing the name in the name box and hitting enter. (I
> > find
> > that a nice way to find out if my formula represented what I really
> > wanted,
> > too.)
> >
> > Your name formula worked fine for me, though.
> >
> > And to make working with names easier, get Jan Karel Pieterse's (with
> > Charles
> > Williams and Matthew Henson) Name Manager:
> >
> > You can find it at:
> > NameManager.Zip from http://www.oaltd.co.uk/mvp
> >
> > <snip>
> > --
> >
> > Dave PetersonThanks Dave- finally figured that out! As noted above- I must have had
> a typo in the Chart Source Data reference. NameManager looks like just
> the tool for me. Looking forward to trying it out.
>
> Just out of curiosity- is this naming behavior documented somewhere?
> I'm usually a darn good researcher, and was really frustrated not to be
> able to find documentation about this.
>
> Thanks again, really appreciate the response!!
>
> --
> hds
> ------------------------------------------------------------------------
> hds's Profile: http://www.excelforum.com/member.php...o&userid=36003
> View this thread: http://www.excelforum.com/showthread...hreadid=557889

--

Dave Peterson

the defined names do NOT show up. To test use f5 goto and type in the name

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"hds" <hds.2adyeo_1151957403.8175@excelforum-nospam.com> wrote in message
news:hds.2adyeo_1151957403.8175@excelforum-nospam.com...
>
> excelent Wrote:
>> ok try take a look at mine sample maby it can give u a hint
>>
>> http://pmexcelent.dk/DynamicShart.xls
> THANK YOU! It seems to be working for me now in my own charts. Here's
> what I'm seeing:
>
> 1) the behavior I've described in this thread seems to be correct. In
> other words- the Insert>Name box shows all names, but the same names do
> not show up in the Name Box on the toolbar when the Names refer to
> formulas (at least- not when referring to Offset formula).
>
> 2) In spite of my earlier post- Charts WILL recognize names that do not
> show up in the Name Box. I must have made a typo in my earlier
> attempts. Your chart convinced me to try again.
>
> Thanks so much for the help, I really appreciate it! (And really
> happy my chart now seems to be working )
>
>
> --
> hds
> ------------------------------------------------------------------------
> hds's Profile:
> http://www.excelforum.com/member.php...o&userid=36003
> View this thread: http://www.excelforum.com/showthread...hreadid=557889
>

Originally Posted by Dave Peterson

I've seen it documented in the newsgroups <bg>

then I came to the right place. Thanks again.

I'm not sure if its OK to reply to such an old thread, but I experienced this issue as well, didn't see a sufficient here, then solved it myself.

I also had several named ranges set up through the Name Manager, one of which for some reason didn't appear in the Name Box drop-down list.

I discovered that it was because in the named range definition, I had selected the same cell more than once in the "Refers to" field of the "New Name" pop-up dialog box.

SOLUTION: Re-define the subject Named Range and make sure that you don't include the same cell twice in the "Refers to" definition range, or else it won't show up on the Name Box drop-down list.