add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Ex-Eng-ch06What is a Name?
A Name can replace the reference of a cell or cells, a row, a column and/or a sheet. After you define it, a name is saved along with its reference in the Name box.
The Name box appears at the left end of the formula bar.
Why Define Names?
The definition of a Name for a cell or a range of cells is essential for efficient and professional work.
1. A Name reduces the length of a formula. For example, instead of typing the reference =Sheet2!A1, you can enter the Name that you defined as =David, where David is the name of Cell A1 in Sheet 2.
2. Use Names to move quickly to another reference in the workbook, regardless of the name of the sheet. This is an excellent technique to use when handling workbooks that have a large number of sheets.
3. A Name takes the place of an absolute reference. It makes copying and pasting formulas easy.
4. Updating a Name’s reference allows you to dynamically update calculation ranges for formulas, update data ranges in a PivotTable, update Validation lists, or input ranges in a Combo Box.
Name syntax
© A Name must begin with a text character and not a number (after that, you can add numbers).
© A Name consists of adjacent characters. Join two words by using an underscore. For example, the name Excel Book is illegal. Type Excel_Book.
© You cannot use a Name that could otherwise be confused as a cell reference. For example, you cannot use A1 or IS2002 because these are already cell references.
© There is no limit on the number of names you can define.
© Be sure to define unique names for a specific workbook. Defining a Name that resembles names in other sheets only complicates your work.
Defining a Name
There are two ways to define a Name:
Type the text directly in the Name box
1. Select Cell A1.
2. In the Name box, type the text.
3. Press Enter.
Use the Define Name dialog box
1. Select cell B1.
2. Press Ctrl+F3, or from the Insert menu, select Name, Define…
3. Type the text in the Names in workbook box.
4. Click OK.
Saving names
A workbook saves only the Names defined in that workbook.
Deleting names
Press Ctrl+F3, select the Name, and click Delete.
Tip – Delete unnecessary Names
1. Make it easier to locate a Name. A large number of Names makes it more difficult to locate a specific Name.
2. Names create references. A reference is a link to an address in the active workbook or in another workbook. To delete unnecessary links, see the Changing links/deleting unwanted links section of Chapter 7, Formulas, Links.
Changing a reference
Press Ctrl+F3, and select the Name. In the Refers to… box at the bottom of the dialog box, change the reference manually, and then click OK.
Reviewing names
1. Select any cell in the sheet.
2. Press F3.
3. Click Paste List.
The list of Names in the workbook is pasted into the sheet. Check the list, locate unnecessary Names or those with incorrect references, and delete them. In the Define Names dialog box, press Ctrl+F3.
Automatically defining Names according to the text in the top row and the left column
1. Open a workbook with a data range. There is text in the top row and in the left column.
2. Select the current region and press Ctrl+*.
3. Press Ctrl+Shift+F3, or from the Insert menu, select Name, Create.
4. Select the options Top row and Left column.
5. Click OK.
Open the Name box and check to see that the names have been defined.
Entering and Pasting a Name in a Formula
The most important reason for defining Names is so that you can paste them in formulas. See figure.
The default for a Name’s definition is Absolute Reference. In the Define Name dialog box, notice that the Refers to box contains a $ sign in the NAME reference.
Plan your work and define Names before beginning to enter formulas in the sheet’s cells.
Automatically entering a name in a formula
1. Type some numbers in Cells B1:B10.
2. Select Cells B1:B10, and define a Name for the selected range. Press Ctrl+F3, and type Array in the Names in workbook box.
3. Select Cell B11.
4. Press Alt+= or click the AutoSum icon, and press Enter.
The formula in cell B11: =SUM(Array). Excel recognizes that the cell range has a Name and enters it automatically in the formula instead of the ordinary reference.
Pasting a name in a formula
1. Select another cell in the sheet, such as E1.
2. Press Alt+= or click the AutoSum icon.
3. Press F3, or from the Insert menu, select Name, Paste.
4. Select the name Array, and click OK.
5. Press Enter.
The result – the formula for Cell E1 is: =SUM(Array).
Note
If you define Names after entering formulas in the sheet’s cells, the formula will not reflect the Names and you will not have a readable formula.
Example: Type some numbers in the cell range A1:A10.
Select the range A1:A10, and press Alt+= or click the AutoSum icon (sigma).
The result – the formula in cell A11 is =SUM(A1:A10).
In the example, no Name was defined for the cell range before entering the formula.
Pasting Names in a formula’s argument boxes
In the sheet illustrated in the figure, define Names for the columns based on the text in the first row.
Press Ctrl+*, and then press Ctrl+Shift+F3. In the Create Names dialog box, select the first option – Top row. Click OK.
The result
Names were defined for the data ranges in each column. The reference range for the Name January is =Sheet1!$B$2:$B$11.
Enter a formula to total the data for the first quarter.
1. Select another sheet in the workbook, and select a cell in it.
2. Type the formula =Sum.
3. Press Ctrl+A.
4. Select the first argument box and press F3.
5. Select the name January_2002, and click OK.
6. Paste the names February_2002 and March_2002 in the next two argument boxes.
7. Click OK.
You have entered the following formula into the cell: =SUM(January_2002, February_2002, March_2002).
The advantages of working with names, as in the example, include:
1. You can enter a formula into a cell without selecting a range in the sheet. It is easy and prevents mistakes.
2. The formula is easy to read and understand.
3. It is easy to review – from the Name box, select one of the names that was entered into the formula. In the sheet where the Name is defined, the range containing that Name is quickly selected.
Replacing a reference in a formula with a newly defined name, after entering the formula into the cell
Now you understand the reason to define names and paste them into formulas, but there are no Names in the files in which you have already created reports and tables. Define Names and replace their references.
From the Insert menu, select Name, Apply. Select the Name(s) that you want to replace, and click OK.
Saving a Formula or Numeric Value for Reuse in the Name Box
When working in Excel, you use certain formulas over and over again. Instead, you can enter a formula and save it in the Define Name dialog box in the Refers to box.
Example: A formula for calculating the number of the previous year:
=Year(Today())-1
Explanation: The formula calculates and returns the number of the previous year – 2002.
1. Press Ctrl+F3.
2. In the Names in workbook box, type LastYear.
3. Type the formula =YEAR(TODAY())-1 in the Refers to box.
4. Click OK.
Enter the formula into a cell in the sheet.
1. Type the = sign. Press F3.
2. Select the name LastYear, and click OK.
Saving values in the Define Name dialog box
In the Define Name dialog box, you can save values in the Refers to… box, just as you saved the formula in the example above.
Example: The rate of exchange for the euro, defined with the Name Euro, is set at 0.88.
Type the amount in Cell A1.
In Cell B1, type the formula =A1/Euro. You do not have to type the text Euro. Instead, use the F3 shortcut.
Updating a value that was saved as a Name
Press Ctrl+F3, select the name Euro, change the value of the rate of exchange in the Refers to… box, and click OK.
Tip
Save values in the Refers to… box. You can save values such as rates of exchange, indexes, and so forth.
Automatically Updating a Name’s Reference
One of the most important advantages of replacing a formula’s reference with a Name is the ability to automatically update the references of the cells in formulas by updating the Name’s reference.
Updating a Name’s reference is not very efficient if you use many names in your workbook.
One solution is to use a macro that quickly updates all the Names in the workbook.
Another solution, which does not use a macro, is to define a Name with a formula that automatically updates the Name’s reference.
Example: In the range B2:B11, the name January_2002 is defined (income from sales).
The formula =SUM(January_2002) returns the total income from sales in January 2002.
Adding rows that contain data from additional sales receipts requires updating the reference of the Name January_2002.
Solution: In the Define Name dialog box, type a formula in the Refers to… box and save the formula with the Name you defined.
Formula for updating an automatic reference, with a vertical range of cells
The formula =OFFSET('1'!$A$2,0,0,COUNTA('1'!$A:$A))
Explanation: The syntax of the formula OFFSET – (Reference,Rows,Cols,Height,Width)
Offset Reference – the reference of the first cell in the range (in the example, A2).
Rows,Cols – the number of rows and columns, moving from the initial cell (for example, 0 rows and 0 columns).
Height,Width – the height and width, moving from the initial cell.
In the example, height is calculated by the formula CountA (a formula that returns the number of cells that are not empty in the range). Width has been omitted; in the example, there is no need to determine the width.
Formula for updating an automatic reference, for the name of the current region (data table)
=OFFSET('1'!$A$1,0,0,COUNTA('1'!$A:$A),COUNTA('1'!$1:$1))
Explanation
Width (columns) and height (rows) of the data table are calculated by the formula CountA, in which the digit ‘1’ is the name of the sheet containing the formula.
Note
Type an absolute reference carefully (add the $ sign before the row and column reference)
Defining a Name and Updating a Range Reference with a Macro
You can define a Name and update a range reference for the Name with the same line of code.
After selecting a cell/range of cells, insert the following line:
Example:
Define a Name and/or update the Name of the cell range A1:A10:
The second line of code is parallel to the shortcut Ctrl+Shift+Down Arrow.
Define a Name and/or update the Name in the active region:
The second line of code is parallel to the shortcut, Ctrl+*.
Note
An easier syntax for adding or updating a name to a workbook is