Dynamic name range to increase range as table gets larger

Posted on 2012-09-21
Last Modified: 2012-09-21
Hi Experts

How woud you use dynamic name ranges to increase the offset formula in the cells to
Expand as the user adds rows and colums to the current table...
Question by:route217
    LVL 33

    Expert Comment

    If the dynamic named range is for the table you don't really need it - you can refer to tables in formulas in other ways.

    If you really need it then it would be the same as for any other data.
    LVL 50

    Accepted Solution


    For data in column A, define a name with this formula, for example "MyRange"


    You can then "clip on" range names for other columns, for example, column B with the same number of rows as MyRange would be


    In Excel 2007 and later, you can insert a "Table" into the spreadsheet and then use structured references to refer to the populated cells of the table. For more info about structured references in tables see

    cheers, teylyn
    LVL 33

    Expert Comment

    Structured references - knew they had a name.:)
    LVL 50

    Expert Comment

    @imnorie -- yeah, and I hate them with all my heart.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now