Discover How To Assign A Formula To A Name With This Excel Tutorial
Strategy: Use a Name, but assign a formula to the name.
Think about what you do when you set up a named range. For instance, you could assign a name of MyData to cell D1, as shown in Fig. 224. However, when you look at this in the Define Name dialog (Insert – Name – Define), you will see that MyData is really equal to a formula called =Sheet1!$D$3, as shown in Fig. 225.
If you understand this, then it is easy to make the leap that any formula can be assigned a name.
You could define a name called GlobalFormula. When you enter =GlobalFormula in the thousand cells on each of the 20 sheets, it will inherit
the formula from GlobalFormula.
Gotcha: Do you remember the topic about R1C1 style addressing? If you were using R1C1 style addressing, then all of the formulas in column C would be identical, as shown in Fig. 226, and this would be an easy task.
However, I realize that no one uses R1C1 style references, so you will have to build this formula the hard way. In A1 style references, each formula is different in each cell, as shown in Fig. 227.
If you want GlobalFormula to =COS(A2), you cannot just write that. You need to develop a formula that takes the COS function of the cell two cells to the left of the current cell. This is possible, but it requires a whole bunch of new functions that you might never have used before. First, look at the InDirect function. =INDIRECT(“A2”) will return the value that is in A2. When you try to use the results of an INDIRECT function in another calculation, it always helps to put the INDIRECT function inside of a SUM function, as shown below. =SUM(INDIRECT(“A2”))
So, if you wanted to take the COS of A2, you could use the following formula.
=COS(SUM(INDIRECT(“A2”))) The next trick to figure out is how to return the text of A2 to refer to a cell. To do this, use the ADDRESS function. =ADDRESS(2,1) will return the text “A2” because A2 is in the 2nd row, first column. =ADDRESS( 52,26) would return “Z52” because this is the fifty-second row, twenty-sixth column. Is there a function that will return the row number of the cell containing the formula? Yes, the ROW function will return the row number of the cell that contains the formula, as shown in Fig. 228.
Similarly, =COLUMN() will return the column number of the cell containing the formula, as shown in Fig. 229.
So, you could write a formula that returns the name of the cell, like this one shown in Fig. 230:
To return the address of a cell two columns to the left of the current cell, add a “–2” after the COLUMN() function, as shown in Fig. 231.
Therefore, the formula that you need to take the COS of the cell two cells
to the left of the cell containing the formula is:
=COS(SUM(INDIRECT(ADDRESS(ROW(),COLUMN()–2)))).
The actual current formula is =COS(A2)/SIN(A2). This is the formula that you would use:
=COS(SUM(INDIRECT(ADDRESS(ROW(),COLUMN()–2))))/ SIN(SU M(INDIRECT(ADDRESS(ROW(),COLUMN()–1))))
From the menu, choose Insert – Name – Define. As shown in Fig. 232, in the Define Name box, type a name, type the formula, and choose Add.
Result: A name is added to the Workbook Names. The name is assigned your formula, as shown in Fig. 233.
Now, in any cell in the workbook, you can use the formula =GlobalFormula, as shown in Fig. 234.
Additional Details: Any time that you need to change the formula, simply redefine it in the Define Name box and choose Add.
Gotcha: The Refers to: box in the Define Name dialog is one of the most maddening things in all of Excel. If you want to edit a formula in that box, it is sometimes impossible. For example, if you want to change this 2 to a 1 (in order to refer to B instead of A), you would have to highlight the 2, as shown in Fig. 235, and type a 1.
If you attempt to use the Left or Right Arrow key to move through the formula, Excel will insert a cell address to the left or right of the current cell. Watch, I’ve placed the cursor after the word Indirect, as shown in Fig. 236.
One press of the Left Arrow key will insert +Sheet1!$B$2 in the formula, as shown in Fig. 237.
The main problem with this is that sometimes the formula is so long that you cannot see the end of the formula in the Refers to box. When you try to click near the end of the field and hit the Right Arrow, you end up adding references to the formula.
The only solution that I have found is to click early in the formula and drag all of the way to the end. This will force Excel to scroll to the end of the formula, as shown in Fig. 238.
You can then click and drag to isolate the portion of the formula that you need to change, as shown in Fig. 239.
Additional Details: I try to come up with reasonable examples for the case studies in this book. One day, as I was writing about the conditional sum wizard, the telephone rang and my friend Dave was on the line with this exact problem. So, with thanks to Dave, you have this incredibly complex solution to what anyone thought would be an easy problem.
Summary: Named Formulas can save the day, although they can be incredibly complex.
Commands Discussed: Insert – Name – Define
Images
