Excel Hacks Ebook [Electronic resources]

Raina Hawley, David Hawley

نسخه متنی -صفحه : 136/ 67
نمايش فراداده

Hack 43 Nest 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, such as the ones shown in Figure 3-8, where cell A1 is a heading, and the list is sorted. Select Insert Names Define. In the Names in Workbook: box, type Names, and in the Refers To: box, enter the following formula:

=OFFSET($A$2,0,0,COUNTA($A$2:$A$1000),1)

Click Add. Now click back into the Names in Workbook: box and enter the name Jnames (J can be any desired letter). In the Refers To: box, enter the following:

=OFFSET(INDIRECT(ADDRESS(MATCH("J*",Names,0)+1,1)),0,0,COUNTIF(Names,"J*"),[RETURN]
1)

where "J*" is a match for the data you wantin this case, names beginning with J). Now click Add. When you 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, as shown in Figure 3-8.

Figure 3-8. A dynamic named range within another dynamic named range

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, simply enter any letter into any unused cell and then name that cell Letter.

Now, select Data Validation, and select List from the Allow: box. Click into the Source: box and enter A*,B*,C*, etc., until all 26 letters of the alphabet are entered as shown in Figure 3-9. Click OK when you're done.

Figure 3-9. A validation list of letters, followed by the wildcard character

Select Insert Names Define, and enter the name "Names" in the Names in Workbook: box. Enter the following formula in the Refers To: box and then click Add:

 =OFFSET($A$2,0,0,COUNTA($A$2:$A$1000),1)

Click back into the Names in Workbook: box and type LetterNames. Then, in the Refers To: box, enter the following formula, and when you're done, click Add and then OK:

=OFFSET(INDIRECT(ADDRESS(MATCH(Letter,Names,0)+1,1)),0,0,COUNTIF(Names,[RETURN]
Letter),1)

The result will look like Figure 3-10.

Figure 3-10. A dynamic named range controlled by the content of another cell

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 new one with the different name you have given it.

To test this, enter any letter into the cell you named Letter, and you should see any data starting with the letter "L" with a marquee around it.