Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Dynamic name range to increase range as table gets larger

Posted on 2012-09-21
4
Medium Priority
?
386 Views
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...
0
Comment
Question by:route217
  • 2
  • 2
4 Comments
 
LVL 36

Expert Comment

by:Norie
ID: 38421252
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.
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38421256
Hello,

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

=Sheet1!$A$1:Index($A:$A,Counta($A:$A))

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

=offset(MyRange,0,1)

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 http://office.microsoft.com/en-us/excel-help/using-structured-references-with-excel-tables-HA010155686.aspx

cheers, teylyn
0
 
LVL 36

Expert Comment

by:Norie
ID: 38421304
Structured references - knew they had a name.:)
0
 
LVL 50
ID: 38421352
@imnorie -- yeah, and I hate them with all my heart.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

580 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