Excel Made Easy

Dynamic name range in Excel

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?

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

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".

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.

You can find similar Excel Questions and Answer hereunder

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

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

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

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

5) How can I extract file name from a full path?

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

7) How can I extract file name from a full path including folder path and file name?

8) The name manager in Excel is very useful to keep an overview and manage all the variable names in Excel. Here how to use the name manager in VBA

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

10) How do I get the 2nd highest number in a range of numbers?