Hack 41 Create Custom Functions Using Names

Although referencing data by name is convenient , it's sometimes more helpful to store a constant value or even a formula, especially if you've been creating custom functions in VBA.

Assume you have a tax rate of 10%, which you need to use throughout your workbook for various calculations. Instead of entering the value 10% ( 0.1 ) into each formula that requires this tax rate, you can enter the word TaxRate and Excel automatically will know that TaxRate has a value of 0.1. Here is how to do this.

Select Insert Name Define, and in the Names in Workbook: box, type TaxRate . In the Refers To: box, enter =0.1 and then click Add.

From this point on, you can enter any formula into any cell , and instead of adding 10% as part of the calculation, you can use the word TaxRate. Probably one of the biggest advantages to using this method is that if and when your tax rate increases or decreases, and your formulas need to reflect this new percentage, you can select Insert Name Define, then select the name TaxRate and just modify it to suit.

To take things a step further with this concept, you can use formulas as your Refers To: range rather than a cell address or constant value. Suppose you want to create a name that, when entered into a cell, automatically returns the SUM of the 10 cells immediately above it.

Select cell A11 on any worksheet and then select Insert Name Define. In the Names in Workbook: box, type the name Total . In the Refers To: box, type the following:

 =SUM(A1:A10)

Click Add, then OK.

Enter any 10 numbers in any column starting from row 1. Now come down to row 11 of the same column and type the following:

 =Total

The name Total automatically will return the SUM of the 10 cells you just entered in A1:A10.

If you want to create a similarly named formula that is not restricted to only 10 cells, but rather, includes all the cells directly above whatever row happens to contain =Total , follow these steps.

Select cell B11 and then select Insert Name Define. Click the name Total.

Examine the Refers To: box, which will say =SUM(B1:B10) . This enables you to create named formulas. In other words, because you did not make the column references absolute for the original name Total, it always will reference the column you use it in.

Now, click the Refers To: box and change the formula to the following:

 =SUM(B:B10)

Click Add, then OK.

Select any row in any column other than row 1 and enter =Total , and you automatically will get the SUM of all the cells above where you enter this, regardless of how many rows there are. This is because you anchored the row number 1 by making it an absolute reference, yet left the reference to cell B10 as a relative reference, meaning it always will end up being the cell immediately above where you entered the named formula =Total .

By combining this hack with one of Excel's standard, although little known, featuresthe intersect methodit's possible to create sophisticated lookup functions. If you are not aware of how the intersect method works, here is a small example to get you acquainted.

In cell A1, enter the heading Name , in cell B1, enter the heading Pay , and in cell C1, enter the heading Title . Enter Bill in cell A2 and Fred in cell A3.

Enter 10 in cell B2 and 20 in cell B3. Enter Mr in cell C2 and Dr in cell C3. Now, select the range A1:C3 and then select Insert Name Create. Ensure that both the top row and left column checkboxes are checked, then click OK.

Select any cell outside your table and enter =Fred Title . You should get the correct title for the name Fred.

The space between the words Fred and Title is important, as this is what Excel understands as the intersect operator.

Building on this concept, you can combine this capability with Excel's named formula capabilities to again make your spreadsheets not only easier to use, but also much easier to read and understand.

Assume that you have a table set up on a spreadsheet in a fashion similar to that shown in Figure 3-2, and that you are using this table to create your names in Excel.

Figure 3-2. Shows Table and Create Names dialog

Once you create the names for the table, you will see that Excel automatically places an underscore in the spaces between two or more words. This is because the names of named ranges cannot contain a space.

Select Insert Name Define, and in the Names in Workbook: box, enter FredsPayRate . In the Refers To: box, type =Fred_Jones Pay_Rate and then click Add, as shown in Figure 3-3.

Figure 3-3. Created Name look-up

Now, in any cell outside your table, enter the following:

 =FredsPayRate

The pay rate for Fred will be returned automatically.

You might want to experiment with intersections to see how they work best in your projects.