Linking Spreadsheet Data: setting up multiple sheets; creating references to other sheets and other documents

Hello students, welcome to Kwickprep. Your class keeps Mid term marks on one sheet and the final result on another. If a Mid term mark changes, must you fix the result sheet too? Not if the sheets are linked. Today we will set up multiple sheets and create references to other sheets and other documents in LibreOffice Calc.

First, what is linking? Linking means a formula in one place reads the value of a cell in another place. When the source cell changes, the linked cell changes by itself. You never copy numbers again, so the two sheets can never disagree. Links work between sheets of one file, and even between two different files.

Now let us look at sheets. A spreadsheet file is like a notebook with many pages, and each page is called a sheet. The sheet tabs sit at the bottom left of the Calc window. A new file starts with one sheet named Sheet1. Click any tab to open that sheet.

Now let us set up more sheets. To insert a sheet, open the Sheet menu and choose Insert Sheet, where you can pick its position and name. For a quick sheet, click the plus button near the tabs. To rename a sheet, double-click its tab and type a clear name, like Mid. To move or copy a sheet, right-click its tab and choose Move or Copy Sheet. To delete one, right-click the tab and choose Delete Sheet.

For our example, we plan three sheets. The Mid sheet holds marks of the mid term. The Final sheet holds marks of the final exam. The Result sheet adds both. Keep sheet names short and without spaces, because you will type them inside formulas.

In Calc, a reference to another sheet is the sheet name, a dot, then the cell address. The first line reads cell B2 of the Mid sheet. The dollar sign before the sheet name, in the second line, makes the sheet part absolute, and Calc often adds it for you. The third line adds B2 of Mid and B2 of Final, which gives the total on the Result sheet.

You do not have to type the address. Go to the Result sheet and click cell B2. Type an equals sign to start a formula. Click the Mid tab, then click cell B2. Type a plus sign. Click the Final tab, then click B2 there. Press Enter, and Calc writes the full formula for you.

Pause and predict. Before, Mid B2 is forty and Final B2 is thirty eight. Result B2 holds the formula, so it shows seventy eight. Now the teacher corrects Mid B2 to forty five, and Final stays thirty eight. What does Result B2 show? It updates by itself to eighty three.

Sometimes the data is in a different file, for example a marks file kept by another teacher. A reference to another document links a cell in that other file. Open both files. In your file, type an equals sign, then switch to the other file and click the cell. Press Enter, and Calc writes the full path of that file into the formula.

Here is the formula Calc writes, all on one line. First comes the file path inside single quotes, which starts with file colon and three slashes. Then a hash sign, and then the sheet name and cell, like dollar Mid dot B2.

Links to other files need a little care. If you rename or move the source file, the link breaks and shows an error. To refresh or fix links, open the Edit menu and choose Links to External Files. When you open a file with links, Calc may ask about updating them, so choose to update them.

Let us revise what we learned today. Linking means a formula reads a cell somewhere else and updates by itself. Insert sheets from the Sheet menu, and double-click a tab to rename it. A reference to another sheet is the sheet name, a dot, and the cell. A reference to another file adds the file path in quotes and a hash sign. The easy way is to type equals and click the cell you want.