Cell and Range References and Names

 

Relative and Absolute Addressing.  After having made use of relative and absolute addressing I want to present a little summary.  Cell references occur in four different ways.

  • Relative - the reference is fully relative - when it is copied it adjusts completely.  For example B3.

  • Absolute - the reference is fully absolute - when it is copied no aspect of the coordinates associated with the cell adjust.  This has $ before both coordinates.  For example $B$3.

  • Row absolute - the reference is partially absolute - when it is copied the column part adjusts, but the row coordinate doesn't change.  For example B$3.

  • Column Absolute - the reference is partially absolute - when it is copied the row part adjusts, but the column coordinate doesn't change.  For example $B3.

It is hopefully obvious by now that you use different approaches to addressing depending on how you want cell references in a formula to change when the formula is copied.

 

Referencing Other Sheets or Workbooks.  It is also possible to access information from other sheets within your current workbook, or even from other workbooks.  This is done with the ! operator.

Let's say you need the Total Sales figure from another sheet in your workbook.  The name of the sheet is Sheet3 since you haven't yet bothered to rename it.  The information is in cell C13 in that sheet.  To access that information you would use the name

Sheet3!C13

This can also be used to access this information from a completely different workbook.  Assume the Total Sales figure is in a workbook called  Budget.xls.  Then to access this information in another workbook you would use the reference

[Budget.xls]Sheet3!C13

Much of this referencing is made easier at the time of use by locating the cell in the desired open workbook and spreadsheet by pointing and clicking on the cell.  At this point the reference address will be automatically filled in.

If the name includes spaces in it you need to enclose the reference in single quotation marks.  Assume the workbook is named Budget & Sales.xls.  Then the reference would be

'[Budget & Sales.xls]Sheet3'!C13

Finally, if the workbook is closed then you need to include a complete path such as

'C:\My Documents\OOPSSVB\[Budget & Sales.xls]Sheet3'!C13

 

Naming Cells and Ranges.   It is often worthwhile to make the extra effort to give a cell or a range of cells a name.  This allows the developer to use the name in place of typing a cell address or range.  This can have many advantages, particularly when someone else is looking at your spreadsheet or you come back to it after not having used it for a while.

For example, in the MortgageBasics.xls workbook

  1. Start working with a new spreadsheet and rename it Specific Property Named Cells.
  2. Copy the cells from an earlier sheet 'Specific Property'!A1 ... I27 to this new sheet.  We will now work with this copy.
  3. Select cell B21.
  4. Click on the Insert menu.
  5. Select the Name item and then select the Define item in the submenu that appears.
  6. Select the phrase Interest_Rate from the window or type it in if necessary.
  7. Click on OK.
  8. Select cell B23.
  9. Click on the Insert menu.
  10. Select the Name item and then select the Define item in the submenu that appears.
  11. Select the phrase Loan_Duration from the window or type it in if necessary.  You may have to modify the phrase that appears slightly.
  12. Click on OK.
  13. Select cell B25.
  14. Click on the Insert menu.
  15. Select the Name item and then select the Define item in the submenu that appears.
  16. Select the phrase Price_of_the_Property from the window or type it in if necessary.
  17. Click on OK.
  18. Select cell B27.
  19. Click on the Insert menu.
  20. Select the Name item and then select the Define item in the submenu that appears.
  21. Select the phrase Down_Payment from the window or type it in if necessary.
  22. Click on OK.
  23. Select cell C4.
  24. Click on the Insert menu.
  25. Select the Name item and then select the Define item in the submenu that appears.
  26. Select the phrase Mortgage_Amount from the window or type it in if necessary.
  27. Click on OK.

Now it will be possible to use these names in the cell formulas.  It would be just as easy to name a range of cells after selecting them.

The following image shows the Define dialog window after all five of these names have been defined.

 

 

If we had defined these named cells earlier we could have used them directly when we created the cell formulas.  But, in this instance we already created the cell formulas and now want to modify them to make use of these names.  We will carry this out for only one of the formulas on this spreadsheet.  From this example it should be clear how this can be repeated to modify other cell formulas as desired.

Originally, the cell formula for the payment amount in cell C5 on the Specific Property sheet was

=PMT($B5/12,12*$B$23,C$4)

Now you should select the cell C5 on the Specific Property Named Cells spreadsheet and perform the following steps.

  1. Click on the Insert menu.

  2. Select the Name item and then select the Apply item in the submenu that appears.

  3. Click on the Loan_Duration and Mortgage_Amount items in the dialogue window so that it looks like the following.

 

 

  1. Click on OK and you should now get the following cell formula.

=PMT($B5/12,12*Loan_Duration,Mortgage_Amount)

This has significantly more meaning to an outside viewer and it will also help you remember all of the meanings of the formulas when you refer back to the sheet in the future.

 

Naming Constants.  It is also possible to use this naming process to define constants.  I will work a very simple example finding the area of a circle.
  1. In cell A1 type The Area of Circle.
  2. Format the font, background and border appropriately.  Align the title across cells A1 ... B1.
  3. In cell A3 type Radius= and format appropriately.
  4. Define the name Radius for cell B3.
  5. In cell A5 type The Area is.
  6. Define the name The_Area_is for cell B5.
  7. Define a constant for PI by clicking on the Insert menu.
  8. Select the Name item and then the Define item in the submenu that appears.
  9. Type the word  PI  in the   Names in workbook:   textbox in the dialog.
  10. Type   3.14159  in the   Refers to:   textbox.

The dialog window should look like the following.

 

 

  1. Now you want to define the formula in cell B5 as

=PI*Radius^2

Now the spreadsheet should contain cells like the following when you put a number in for the Radius in cell B3.

 

 

Some Other Issues.  You can apply names to entire columns and rows.  You can also name formulas and any other objects in a workbook.  Probably the most important remaining issue associated with naming has to do with the scope of a name.  The scope of the name refers to how long or in what locations it remains unique.  The default scope for any name is the workbook.  By default you can only use each name once within a workbook.  You can then use it again in another workbook, which can be very important.  But in order to limit the scope of a name further you need to follow special steps.