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