56 About Names as Addresses

56 About Names as Addresses

The makers of OpenOffice.org designed Calc to be watching over your shoulder, ready to help you when possible. One way that Calc does this is to look at the labels you place in your spreadsheets and use them as range names when appropriate.

Before You Begin

48 About Calc Ranges

49 Create a Range

Consider the sheet in the following figure. The totals do not align well with any specific row or column. In other words, cell B4 is going to hold the total of the four Miami estimates, not G7 , where the total could easily be placed by selecting G7 and clicking the Sum button. To compute the total projections of Miami, for example, requires more than clicking the Sum button, because the total entry for Miami does not fall right below the column of Miami- related data. As you see in 52 Enter Calc Functions , the Sum button is useful when you are totaling a column or a row and the total is to appear directly at the end of that column or row.

Of course, you could type the Sum() function in the appropriate cells of column B , but doing so ( especially if the table were much larger) is time-consuming and error-prone . To total the Miami sales projections, for example, you would enter this into cell B4 :

  =Sum(H4; H10)  

Totals do not always fall at the end of a row or column.

TIP

You rarely can name too many ranges. If you create a spreadsheet that you plan to add to quite a bit, create range names as you go so that subsequent formulas you enter will be able to rely on those names. For very small sheets, though, sometimes naming ranges is more trouble than its worth.

Likewise, you would have to enter every other range for the rest of the totals.

One improvement would be to create and name the following ranges from the sheet: Est2005 , Est2006 , Est2007 , Est2008 , Denver , Tulsa , Miami , and New York . At least entering the Sum() functions would be simpler. For Miami's total, you would type =Sum(Miami) into cell B4 . If you added data to the table, your range names would expand appropriately, keeping the totals accurate.

In spite of some advantages to the range names, naming six ranges for this small table is a lot of work. It's simpler and quicker ”although, as just stated, more error-prone and more difficult to maintain ”if you create the ranges individually for totals.

Fortunately, Calc is smart. Notice the headings over the columns of data: Denver , Tulsa , Miami , and New York . These are not range names; rather, they are just labels typed over the columns to label the data. The same is true for the rows with these labels: Est 2005 , Est 2006 , Est 2007 , and Est 2008 .

Although the column labels are not range names, you can often treat such column and row headings as though they are range names!

Therefore, you can enter the following formula into Denver's total cell B2 :

  =Sum(Denver)  

Denver's total computed correctly without range names.

NOTE

If you copy or move a cell that uses a heading or row name to another place in the sheet, Calc moves the cell using absolute addressing (see 44 Copy and Move Formulas ).

Calc did not generate a new range named Denver . All Calc did was make an educated guess that you wanted to calculate the column under the heading Denver . Calc did not confuse the labels Denver in the table with Denver: in column A because column A 's Denver : has a colon following it.

You won't want to rely on Calc's capability to calculate from labels in large and complex sheets because you'll be rearranging such sheets occasionally, and range names are better suited for sheets that you edit often. For smaller sheets, though, using the labels as column and row headings for summing and performing other routine functions makes a lot of sense.

One thing you must keep in mind when using cell labels inside functions and calculations is to enclose the labels in quote marks if they have a blank in them. For example, the following two cell entries would produce errors in this sheet:

 =Sum(New York) =Sum(Est 2007) 

To correct these entries, you must use quotes like this:

 =Sum('New York') =Sum('Est 2007') 

If a label is nothing more than a number, as the year 2004 would be if used as a label in the table, you would not be able to use that label in such calculations, even if you enclosed it inside quotes.

The next figure shows the completed sheet with all the totals. As you can see, all the totals work fine, even though no range names exist.

All totals entered using the table's column and row labels.