Excel Hacks Ebook [Electronic resources] نسخه متنی

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.