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

[hds]

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]

should have noted- am using Excel 2003. Thanks!

 

[Biff]

See if this helps:

http://www.peltiertech.com/Excel/Charts/DynamicLast12.html

Biff

 

[Don Guillett]

to use a defined name range as the source

=yourworkbookname.xls!yourdefinedname

 

[hds]

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]

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.

 

[Guest]

what formula do u use in the datavalidation-list ?





"hds" skrev:

 

[hds]

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.

 

[Guest]

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

http://pmexcelent.dk/DynamicShart.xls

 

[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

 

[hds]

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'
what I'm seeing:

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

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

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

 

[hds]

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 th
name and
hit enter) or by typing the name in the name box and hitting enter. (
find
that a nice way to find out if my formula represented what I reall
wanted,
too.)

Your name formula worked fine for me, though.

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

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

<snip>

a typo in the Chart Source Data reference. NameManager looks like jus
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 b
able to find documentation about this.

Thanks again, really appreciate the response!! :

 

[Dave Peterson]

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.

 

[Don Guillett]

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

 

[hds]

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

then I came to the right place. Thanks again.