dynamic name range in Excel

Q99. In Excel, how can I set up a dynamic named range that expands automatically when new items are added to the list?

For example, I have set up a named range "SWITZERLAND" that covers cells "Q3:Q7". However if I add new cities in Q8 and beyond, I will have to maully update the named range.

Is there are an automatic way of making the named range expand as needed?

excel dynamic name range

After adding a test entry "NewCity' in cell Q8, I am not able to find that in the drop down list.

excel excel dynamic name range

To do it in Excel, here is the answer:

a) Click on "Name Manager" under "Formulas". Select "SWITZERLAND" under Name. Click Edit.

In the "Edit Name" dialog box that pos up, enter the formula =OFFSET(!$Q$2,0,0,COUNTA($$Q:$Q)-1,1) in "Refers To:" field as shown below.

First argument, $Q$2 is the reference for offset which is the first record.

Third argument, COUNTA($$Q:$Q)-1 counts the total number of non empty fields in Col Q. 1 is subtracted to account for the Title "Switzerland".

excel excel excel dynamic name range

b) After adding a new city "NewCity2" in cell Q8, checking the drop down list indicates that it expands and gets updated when new entries are added.

excel excel excel excel dynamic name range

 

You can find similar Excel Questions and Answer hereunder

1) How can I remove hyperlinks from a range of cells?

2) How can I convert Column numbers into Column names for use in range definition?

3) How can I get an estimate of Standard Deviation of a sample dataset?

4) How can I clear all formats (formats alone not data) from a range of cells?

5) How can I find the count of records that meet a given condition in my raw data table?

6) How do I find the median salary of employees with the same skillset in my Organization?

7) How can I loop through all WorkSheets and get their names using VBA?

8) Sumifs with date range as criteria in Excel

9) Is there a way to easily shade alternate rows in a data range for improving readability?

10) How can I find the slab in which a given value fits?

11) Is there a way I can average a range of numbers even if there is an error value in range?

12) Can I assign a NAME to a constant (similar to declaring constants in coding environment like VBA)?

13) How can I add a Prefix to a set of numbers in a range?

14) How can I quickly remove all blank cells in a data range?

15) How can I extract First Name and Last Name from a cell that has Full name?