You can use an information function (see Chapter 10) to query the path, file name, and sheet name of the currently saved workbook. The workbook must be saved, otherwise an empty string (“”) will be returned. The formula is:

=CELL("filename")

but the function returns all the information in a single string; for example:

C:Excel-FunctionsChapter05[Chapter05_Names.xls]Fct_Names

The text functions in Chapter 8, provide an approach to isolate the individual sections of information; this is often called parsing. This approach can be useful if you want to insert this information directly into the sheet rather than use the text modules &[Path], &[File], and &[Tab], which are available in the worksheet header or footer sections.

Querying the Path of the Current Workbook

The path of the workbook is at the beginning of the string and ends at the left bracket “[“. To isolate the string to the left of this point, the following syntax is used:

=LEFT(CELL("filename"),FIND("[",CELL("filename"))-1)

The FIND() function calculates the position number of the left square bracket, and LEFT() determines the string up to the position of this bracket. The following steps describe how these functions are used:

1. In Excel 2007 and Excel 2010 on the Formulas tab, click the Define Name icon (see Figure 5-1). In Excel 2003, select the Insert/Names/Define menu option.

2. Enter the text Path in the Name text box (Excel 2007 and Excel 2010) or New Name text box (Excel 2003).

3. Enter the formula shown earlier in this section in the Refers To field, and click OK (Excel 2007 and Excel 2010) or Add (Excel 2003).

Figure 5-1. The Defined Names group on the Formulas tab in Excel 2007 and Excel 2010.

Figure 5-2 shows the New Name dialog box in Excel 2007 and Excel 2010, which is different from the old Define Name dialog box.

Figure 5-2. Entering a calculation for the name Path.

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filen
ame"))-(FIND("[",CELL("filename"))+1))

Querying the Current Sheet Name

Isolate the current sheet name with the RIGHT() function or the MID() function. If you use the CELL() information function, make sure that the sheet name displays the tab label on each sheet by defining the optional second argument, which must contain a reference to any worksheet cell. Use the INDIRECT() function for this worksheet cell reference so that Excel doesn’t attach the sheet name to references (for example, Table1!A1). Here we use the MID() function because it is shorter than using the alternative RIGHT() function:

=MID(CELL("filename",INDIRECT("A1")),FIND("]",CELL("filename",
INDIRECT("A1")))+1,255)

Identify the sheet name with the MID() function. The Start_num argument is set by determining the position of the right bracket with the FIND() function. Use the number 255 for the num_chars argument. This covers names up to the maximum allowed length. The following steps show how to use this formula:

1. Select the Define Names command. Enter Sheet name in the Name box.

2. Enter the formula just shown in the Refers To field, and click OK (or Add, for Excel 2003).

Excel 2007 introduced a new tool: the Name Manager (see Figure 5-3). This provides a much improved facility for defining, editing, and managing the names used in a workbook.

Figure 5-3. The Name Manager in Excel 2007 and Excel 2010 provides significantly better options for working with names.

Assume that your company uses different payment targets for billing. These targets range from immediate payment to due dates in 10, 14, 20, or 30 days. To specify a payment target, you should enter a term that provides the date in a text format:

You can now use the formula =“Please pay the invoice amount by” & PaymentTarget20” to set the payment target in an invoice form by specifying the name—PaymentTarget10, PaymentTarget14, and so on—in the formula.

The following example demonstrates how powerful names can be: Assume that you have a list to which entries are added on a daily or weekly basis. When you evaluate the list, you want the calculation to expand automatically to cover the additional entries. How can you achieve this?

Assume that each day you enter fitness training data into a table. Column A contains the date, column B the time taken, and column C the distance covered (see Figure 5-4).

Figure 5-4. The names point to the extended range if new values are entered

The maximum number of rows in the worksheet will be 367 (including the title row and 366 leap year days) if each calendar year has its own worksheet. Create a name that will reference the maximum used range.

The solution lies in how the names are assigned:

To identify the dynamic range, you can use the following function to return a reference that is offset from a specified reference:

OFFSET(reference,rows,columns,height,width)

In this case, the range starts in the row defined by the name Start, with the appropriate column selected by the Offset parameter. The MAX function identifies the number of numeric entries in the Entry range and is used in the OFFSET function to extend the range out to this point.

It is important to remember that the names Hours and Miles are based on the entries made in the Date column and thus this example relies on a date being entered for each line of information.

Add new entries to the data to the test the ranges. Press the F5 key to open the Go To dialog box and locate the reference again. You will notice that the name now points to the extended range, and the calculated values for sessions, time, and distance are based on the extended range.

Please note that you can also use dynamic range names as a source for charts.