101 Excel 2013 Tips, Tricks and Timesavers (14 page)

If you copy a worksheet that has worksheet-level names, the names are duplicated as worksheet-level names in the new worksheet.

The Name Manager dialog box clearly identifies each name by its scope (see Figure 31-2). If the scope of a name isn’t Workbook, the dialog box lists the sheet on which the name is defined.

Figure 31-2:
The Name Manager makes it easy to distinguish between workbook-level names and worksheet-level names.

Only the worksheet-level names on the current sheet appear in the Name box. Similarly, only worksheet-level names in the current sheet appear in the list when you open the Paste Name dialog box (by pressing F3).

Tip 32: Using Named Constants

This tip describes a useful technique that can remove some clutter from your worksheets: named constants.

Consider a worksheet that generates an invoice and calculates sales tax for a sales amount. The common approach is to insert the sales tax rate value into a cell and then use this cell reference in your formulas. To make things easier, you probably would name this cell something like SalesTax.

You can store your sales tax rate by using a name (and avoid using a cell). Figure 32-1 demonstrates the following steps:

1.
Choose Formulas⇒Defined Names⇒Define Name to open the New Name dialog box.

2.
Type the name (in this case,
SalesTax
) into the Name field.

3.
Specify Workbook as the scope for the name. If you want the name to be valid only on a
particular worksheet, specify the worksheet in the Scope field of the New Name dialog box.

4.
Click the Refers To field, delete its contents, and replace it with a simple formula, such as
=7.5%
.

5.
Click OK to close the dialog box.

Figure 32-1:
Defining a name that refers to a constant.

The preceding steps create a named formula that doesn’t use any cell references. To try it out, enter the following formula into any cell:

=SalesTax


This simple formula returns .075, the result of the formula named SalesTax. Because this named formula always returns the same result, you can think of it as a
named constant.
And you can use this constant in a more complex formula, such as this one:

=A1*SalesTax


A named constant can also consist of text. For example, you can define a constant for a company’s name. You can use the New Name dialog box to create the following formula, named MSFT:

=”Microsoft Corporation”


Then you can use a cell formula, such as this one:

=”Annual Report: “&MSFT


This formula returns the text
Annual Report: Microsoft Corporation
.

Names that don’t refer to ranges don’t appear in the Name box or in the Go To dialog box (which appears when you press F5). This makes sense because these constants don’t reside anywhere tangible. They do, however, appear in the Paste Name dialog box (which appears when you press F3) and in the intellisense drop-down list when you’re creating a formula. This does make sense because you use these names in formulas.

As you might expect, you can change the value of the constant at any time by using the Name Manager dialog box (choose Formulas⇒Defined Names⇒Name Manager). Just click the Edit button to open the Edit Name dialog box. Then change the value in the Refers To field. When you close the dialog box, Excel uses the new value to recalculate the formulas that use this name.

Tip 33: Sending Personalized E-Mail from Excel

This tip describes a method to compose and send short customized e-mail messages from Excel, using the HYPERLINK function.

About the HYPERLINK function

The HYPERLINK function creates a link that, when clicked, activates your default browser and navigates to a web page. The function takes two arguments: the URL and the text that’s displayed in the cell. For example, this formula creates a link to my website:

=HYPERLINK(“http://spreadsheetpage.com”,”Spreadsheet Page”)


A URL can also contain an e-mail address. When clicked, the hyperlink opens the new message window of your default e-mail client, with the e-mail address in the “To” field. Here’s an example:

=HYPERLINK(“mailto:[email protected]”,”Email support”)


If you don’t have a local default e-mail client and rely on a web-based e-mail service (such as Gmail or Hotmail), this technique won’t work.

You can also include a subject line. Here’s an example of the first argument for the HYPERLINK function that includes an e-mail subject line:

“mailto:[email protected]?subject=Help!”


In addition, you can include a short default message:

“mailto:[email protected]?subject=Help me!&body=I don’t understand this.”


Things get a bit more complicated if you want to include a line break in the message body. If that’s the case, you need to “percent encode” the line break by using this code: %0A. Here’s an example that inserts two line breaks into the e-mail body:

“mailto:[email protected]?subject=Help me!&body=I don’t understand this.%0A%0AYour customer”


Technically, all non-alphanumeric characters should be encoded, including spaces. You can use the ENCODEURL function (introduced in Excel 2013) to encode any text string.

A practical example using HYPERLINK

Figure 33-1 shows a worksheet set up to make it easy to send a brief personalized e-mail from Excel.

Figure 33-1:
The hyperlink in cell B8, when clicked, opens the default e-mail client with a message ready
to send.

Here’s a description of each cell:

→
B1:
The first name of the e-mail recipient.

→
B2:
The e-mail address.

→
B3:
The subject line for the e-mail address.

→
B4:
The subject line, encoded using this formula:

=ENCODEURL(B3)


→
B5:
The body of the message. This cell uses wrap text formatting, and line breaks were entered by using Alt+Enter.

→
B6:
The body text, encoded using this formula:

=ENCODEURL(B5)


→
B7:
The first argument for the HYPERLINK function, constructed by concatenating text and cells. The formula is

=”mailto:”&B2&”?subject=”&(B4)&”&body=”&B6&””


→
B8:
This cell contains a formula that uses the HYPERLINK function:

=HYPERLINK(B7,”Click to send”)


The length of the text in cell B7 (the first argument for the HYPERLINK function) cannot exceed 255 characters. If the text is too long, cell B8 displays a #VALUE error.