Referencing Cells in Calc

If you are working on a spreadsheet containing multiple sheets that all share a common set of categories as a first column, and these category titles may change with time, you don't need to modify them on each and every sheet, just use cell references.

Background

Cell References in OpenOffice.org Calc have the following general syntax:

FILENAME#SHEETNAME.CELLADDRESS

with

• FILENAME being the name of the file that contains the value to be referenced
• SHEETNAME being the name of the sheet in that file that contains the value to be referenced
• CELLADDRESS being the name of the cell that contains the value to be referenced
  

If you are referencing within the same file, the FILENAME# part is optional, if you are referencing within the same sheet, the SHEETNAME. part is optional, too.

Example

On Sheet1, Mary had a little lamb, and you would like Mary to have a little lamb on Sheet2, too:

Go to Sheet2, click on the first cell and insert the magic formula:

=Sheet1.A1

telling Calc to insert a reference to cell A1 on Sheet1 in the current document. That cell says Mary and so does this cell after entering the formula. But as you can see on its formula bar, it just points to cell A1 on Sheet1 and displays its content:

Now for the rest of the rows, you don't need to enter the formula again. Just select the range of cells:

and select " Edit - Fill - Down" from the main menu to let OpenOffice.org fill the cell contents for you:

Now, if you decide that Mary should rather have a cow, she'll have it on all referenced sheets as well:

Printing All Formulas Used in Your Calc Sheets

Sometimes you want a printed reference of the formulas that you entered to a spreadsheet. You can get a printout of all formulas by a few mouse clicks.

Most Calc users already know that it is easy to see the formulas on screen:

• Choose Tools - Options - OOo Calc - View, then enable Display - Formulas.

Now, to print the formulas you have to look at a different option.

• Choose Format - Page - Sheet, then enable Print - Formulas. Print the spreadsheet.

Don't forget to reset the Print - Formulas setting after printing, so you will get the calculated values again on later printouts.

If you need a file that lists the formulas, enable printing formulas as above, then choose File - Export to PDF. The resulting PDF file lists all formulas in the defined print range.