Named Ranges

[3G]

Hi there-

I read up on the "dynamic named ranges" topic, and, wonder if I need to use them instead of referencing a larger range of data than actually exists, with the expectation that data might eventually show up.


Basically, I currently have a list of 125 documents in a spreadsheet. There are 125 rows of data (one row per document), and, 20 columns of data for each row (has dates, and data about each doc). Now, this list is constantly growing, and, I use a lot of the data in the columns for reporting. Currently, my references & ranges point through to the cell $A$1:$U130 with the hopes that eventually, if we add more documents to the list, I don't have to change the range.


Should I be using the dynamic named ranges? If so, with so many columns of data, how do I set it up so that each column is populated when I add a new Document ID Number (Column A)?

 

[Luke M]

Usually you use dynamic ranges (or any other formula) as an output, something that manipulates the data in your table. What method are you currently using to fill in the other columns when you input the Document ID?

 

[dan_l]

Just set up the dynamic range to be governed by the height of column A.

 

[Hui]

3G


Yes this is an ideal application for using with Dynamic Named Ranges


The type of Ranges whether it be a single column per field or the whole data area is dependent on what and how you are querying the data.


If you are extracting single records or field values you can use the whole data area and use an Index(Match()) to retrieve values


If you are summing data generally better to use a Named Range per column and sum using Sum(Offset()) or Sumproduct())

 

[AZExcel]

Dynamic Named Range is great for what you are attempting... You can also achieve the automatic expansion of a range by converting to a table in some cases.