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
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
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
=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
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
Select any cell outside your table and enter =Fred Title. You should get the correct title for the name Fred.
|
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.
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
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.