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

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$1: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.