Dynamic Ranges for Maximum Flexibility

A dynamic named range that resides within another dynamic named range can be very useful for things such as long lists of names.

For example, it's possible to create a named range called Jnames that refers to all the names in a sorted list beginning with the letter J.

Start with a list of names in column A, where cell A1 is a heading and the list is sorted. Then follow these steps:

1. Select Home → Defined Names → Name Manager → New(pre-2007, Insert → Name → Define).
2. Enter Names in the Names: box and the following formula in the Refers To: box:
   =OFFSET($A$2,0,0,COUNTA($A$2:$A$1000),1)
3. Click OK, then New (pre-2007, click Add, then enter the new name).
4. Nowclick back into the Names: box and enter the name Jnames (J can be any desired letter).
5. In the Refers To: box, enter the following:
   =OFFSET(INDIRECT(ADDRESS(MATCH("J*",Names,0)+1,1)),0,0,COUNTIF(Names,"J*"), 1)
   where "J*" is a match for the data you want-in this case, names beginning with J).
6. Click OK.
7. In the Name Manager, select Jnames and click back into the Refers To: box where the function is. All the names beginning with the letter J will have a marquee around them.

If you want, you can create one named range for each letter of the alphabet, but perhaps a better option is to have the named range change according to a letter that you type into a cell on a worksheet. To do this, follow these steps:

1. Start by simply entering any letter into any unused cell and then name that cell Letter.
2. Now, select Data → Data Tools → Data Validation (pre-2007, Data → Validation).
3. Select List from the Allow: box.
4. Click into the Source: box and enter A*,B*,C*, etc., until all 26 letters of the alphabet are entered. Click OK when you're done.
5. Now, select Formulas → Defined Names → Name Manager → New(pre-2007, Insert → Name → Define).
6. Enter Names in the Names: box and the following formula in the Refers To: box:
   =OFFSET($A$2,0,0,COUNTA($A$2:$A$1000),1)
7. Click OK, then New.
8. Click back into the Names: box and type LetterNames.
9. Then, in the Refers To: box, enter the following formula:
   =OFFSET(INDIRECT(ADDRESS(MATCH(Letter,Names,0)+1,1)),0,0,COUNTIF (Names, Letter),1)
10. When you're done, click OK, then Close.

You don't have to retype the formulas from scratch for the dynamic named ranges. Instead, while working in the Define Name dialog, click an existing dynamic named range, overtype the name that appears in the Names in Workbook: box, then move down to the Refers To: box, modify as needed, and click Add. This will not replace the original dynamic named range, but rather, add a totally newone with the different name you have given it.

To test this, select a letter from the Validation drop-down menu in the cell you named Letter, click into the Name Manager, choose LetterNames, and click the collapse tool to the right of the Refers to: box. You should see any data starting with the letter "L" with a marquee around it.