Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Named Range Automatically expand

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Named Range Automatically expand

    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

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Automatically expand

    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

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Named Range Automatically expand

    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
    Attached Files Attached Files

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Range Automatically expand

    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>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Automatically expand

    <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?

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Named Range Automatically expand

    Check out <A target="_blank" HREF=http://www.wopr.com/w3thelp/help_17.html>Forum Help</A> for further instructions.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Named Range Automatically expand

    URL Tag style 2.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Automatically expand

    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

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Cheltenham, Gloucestershire, England
    Posts
    214
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Automatically expand

    Thanks for this - interesting site

    Cheers

    Roberta

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Range Automatically expand

    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>

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Named Range Automatically expand

    Ooooh, nice site, Michael! You da man!
    <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •