How to use spreadsheet functions on cells across multiple sheets in OpenOffice [Tip]

If you set up a OpenOffice spreadsheet with a variety of sheets, the Consolidate option might come in handy. That’s an option with which you can set up functions for data across multiple sheets. With that you can copy data from a range of sheets and run functions on them.

First, enter a couple of numbers into your spreadsheet. Enter one number in sheet1 and another value in sheet2. Click Data > Consolidate to open the window below.

consolidateThen select a cell to add the function to by entering a cell reference in the Copy results to text box. Click the button beside that text box, and choose a cell to add the function to.

consolidate2Click the button beside the Source data range text box. Select the cell in sheet1 that you entered a number in. Press the Add button to add it to the consolidation range as shown in the shot below.

consolidate3Now click sheet2, and press the button beside Source data range text box. Select the cell you entered a number in on that sheet. Then press the Add button to add that value to the consolidation range.

consolidate4Click the Function drop-down list to choose a function for the two values. Select Sum from that drop-down list. Press the OK button to close the window. The cell you selected to add the function to will now add the cells in sheet1 and 2. Note that the function is not shown in the formula bar when you select the cell.

consolidate5Alternatively, you can also run functions on cells across multiple sheets without the Consolidate option. Select a cell to add the function to, enter the function in the formula bar and select a cell on a sheet to include in it. Select another sheet, hold the Ctrl key and click another cell value to include in the function.

As such, you can now run functions on cells across multiple sheets with or without the Consolidate option. The difference is that the Consolidate option runs the function without placing the formula in the cell.

Related Posts