|
Excel adjusts the definition of names automatically.
| 1 | Assuming you have completed the exercise in lesson C, you will have a cell named Sales and a cell named Costs. |
You can check the definitions of these names.
| • | Use Excel's Insert Names Define command. |
| • | You will see entries for the Sales and Costs names that you have defined already. |
| • | Click on the entry for Sales. You will see its name in the text box at the top, the Name field, and the address of the cell it is associated with in the text box at the bottom, the Refers to field. That cell is Sheet1!$B$1. |
| • | Click on the entry for Costs. Now its details are shown in the top and bottom boxes, and its cell is Sheet1!$B$2 |
| 2 | You will also have three cells containing versions of the profit calculation, differently entered but all showing the same value, 85. One of them contains the formula =B1-B2. |
| 3 | Insert a few rows at the top of the spreadsheet, so that all the non-empty cells move down the worksheet. |
| 4 | Take another look at that profit formula. It won't say =B1-B2 any more, because you have moved what were B1 and B2. As even a beginner with spreadsheets knows, Excel adjusts these formulas in these circumstances, and the formula carries on showing 85. |
| 5 | Two other cells contain versions of the profit calculation. They express it in names, showing =Sales-Costs. And they also show 85, just like the first formula. |
| 6 | We can see why this is by using the Insert | Name | Define command again and inspecting the definitions of Sales and Costs. You'll find they too have been updated, to reflect the changed position of these two cells, just as the coordinates in the Profit formula were. |
| 7 | You can confirm this adjustment in another way. If you press F5, the Goto command, and choose either Sales or Costs from the dialog that appears, you will be taken to the selected cell at its new location. |
No matter how much you move the cells around on the worksheet, by cutting and pasting them somewhere else, by selecting them and dragging them into a new place on the worksheet, or by inserting or deleting rows or columns, Excel will keep track of their new position and updates the coordinate references within the name definitions as necessary, just as it updates the coordinate references within all the formulas.
This is really useful. You might recall that your Cost estimate is in cell B2, but after a worksheet has been developed by you and, perhaps, some colleagues, over several months, it might have made its way to cell K937. How would you know? How could you remember? You don't have to. Just give it a meaningful name, and Excel will track it for you. All you have to do to return to it at any later date is press F5 to activate the Goto command, type Costs or choose it from the list of names that is offered, and click OK or press Enter.
See also
Names tutorial E
|