You can quickly create a named range in Excel, but it doesnt automatically expand to include new items that are added at the end of the list. However, a dynamic Excel named list grows automatically, so heres how to create that type of list.
Create a Dynamic Named Range
If you plan to add new items to a list, create a dynamic named range, by using an OFFSET formula. In this example, the formula entered in the Refers to box is:
=OFFSET[Prices!$B$1,1,0,COUNTA[Prices!$B:$B]-1,2]
- Using cell B1 as the reference cell, the list will start 1 row down, and 0 columns to the right.
- It counts all the items entered in column B, and subtracts 1, because the list wont include the heading in B1.
- The list will be 2 columns wide.
Then, as new items are added to the list, the dynamic named range will automatically expand to include them.
Video: Dynamic Excel Named List
To see the steps for creating a quick named range and a dynamic named ranged, you can watch this video tutorial.
Your browser cant show this frame. Here is a link to the page
_______________