Named Range Automatically expand

[roberta]

I one saw a formula typed in the "refers to" section of the Name, Define dialog box which would make the named range expand if additonal rows/comumns were added. I believe it included Offset and Count along the way! Can anybody help please.

Thanks

Roberta

[Andrew Cronnolly]

Assuming your data starts in A1 the following formula in the "refers to" text box should give you a range that grows/shrinks as you enter/delete data. <pre> =OFFSET($A$1,0,0,COUNTA($A:$A),COUNTA($1:$1))</pre>

Using this method means you cannot use Row 1 or Column A for any other data. You might like to include a sheet name reference in the formula.

Andrew C

[roberta]

Thanks for this - I have tried it however I must be doing something wrong!! I have gone to "define" typed a name and entered the "incantation" into the refers to box. Though it is still there when I go back to the name dialog box it does not appear in the drop down on the toolbar or appear when I press F3 - I must be missing something vital here!

Thanks

[Andrew Cronnolly]

Before you set a range like this it is best to have some data in the range, otherwise Excel cannot calculate the extent of the range. See attached worksheet, which should help. I have created a range "Incantation" which you can play around with.

Andrew

[WebGenii]

Thanks Roberta for the <img src=/S/clever.gif border=0 alt=clever width=15 height=15>term "incantation" I think I'll use it in my next advanced Excel class ... should be good for a few <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

[Michael Evans]

<A target="_blank" HREF=http://www.elementkjournals.com/ime/9603/ime96031.htm>http://www.elementkjournals.com/ime/9603/ime96031.htm</A>This Link may be of help.

OK I surrender how do you enter those neat This Link messages without the whole URL showing?

[WebGenii]

Check out <A target="_blank" HREF=http://www.wopr.com/w3thelp/help_17.html>Forum Help</A> for further instructions.

[JohnBF]

URL Tag style 2.

[roberta]

Thanks for this - I see the two things I was missing - use F5 to go to the range rather than expect to see it on the list and get some data in the range before you start!

Cheers

Roberta

[roberta]

Thanks for this - interesting site

Cheers

Roberta

[Michael Evans]

Found them! Here are two links it has taken me a while to find again. The one on charting is especially good.

<A target="_blank" HREF=http://www.beyondtechnology.com/geeks007.shtml>Dynamic Ranges</A>
<A target="_blank" HREF=http://www.beyondtechnology.com/geeks021.shtml>Charting Dynamic Data</A>

[JohnBF]

Ooooh, nice site, Michael! You da man!
<img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>