How do I set up a Dynamic Named Ranges in Excel 2007

[Aussie Bob C]

I tried "cpearson" Dynamic Ranges
=OFFSET(Spend Rates!$L$5,0,0,COUNTA(Spend Rates!$M:M),1)
to create a dynamic range in Column L starting at row 5.

It appeared in a list for editing, but didn't appear in the Name Box drop
down list.
Have I omitted something or has the syntext changed for Excel 2007?

Tried searching for it in Windows online help, not a thing about Dynamic
Named Ranges came up.

TIA

 

[Bob Phillips]

Dynamic named ranges do not, and never have, appeared in the Names Box. This
is because you can select the name in that box and Excel will select the
range. Unfortunately, Excel seems incapable of evaluating the name upon
selecting to get the actual range, so they omit it.

 

[Bob Phillips]

Best thing to do is to get Jan Karel Pieterse's NameManager utility,
http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp. You will see those names
in this utility, and it is a far better way to manage names anyway.

 

[Aussie Bob C]

Dynamic Named Ranges can still be used in functions & programing Y/N?
Just selecting one in the Names Box is not possible.
--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.

 

[Bob Phillips]

Absolutely, you can use them in a formula, in VBA, and even in Edit>Goto
(Ctl-G) where you can type in that name and it will THEN evaluate and goto
that range.

 

[Herbert Seidenberg]

Convert your named range(s) into an
Excel 2003 List or Excel 2007 Table.
The names of the columns will become dynamic
and appear in the Name Box.
Also check 2007 > Options > Proofing > AutoCorrect options

 

[Aussie Bob C]

I don't seem able to get the Dynamic Named Range in my post to work in VBA.
Name SpendRate
=OFFSET(Spend Rates!$L$5,0,0,COUNTA(Spend Rates!$M:M),1)

I'm trying to copy column L from row 5 down to last row of data.
Range("SpendRates").Copy
Macro stops at this line.

TIA

--
Thank you

Aussie Bob C
Little cost to carry knowledge with you.
Win XP P3 Office 2007 on Mini Mac using VMware.

 

[Bob Phillips]

=OFFSET('Spend Rates'!$L$5,0,0,COUNTA('Spend Rates'!$M:M),1)