Apache OpenOffice Community Forum

Thanks for any assistance on this. Have done fair amount of searching but could not find an answer. Using OO version 2.4.1 with Linux Mint (Ubuntu 8.04).

What if one wants to operate on ALL the current and subsequent values in a column? Normally one specifies a range (C1:C201). I would like to specify C*, as if "*" meant all values in column C.

Situation: Spreadsheet is serving as a day book. Each time there is a transaction, it is entered on a row under columns for TRANSACTION (A), DATE (B), FUNDS IN (C), FUNDS OUT (D), and BALANCE (E). The formula in cell E3, for example, is =E2+C3-D3. Cells in columns C, D, and E are formatted for currency.

Objective is to have a cell at the top (above where this data accumulates) which gives the current balance. This could be achieved if a formula could be written which subtracted the totals of column C from the totals of column D.

Even better would be to operate on all cells of a column below a specified cell.

The sheet is constantly receiving new transactions so a limited range of cells in a column is either going to be rendered invalid when the bottom of the range is passed or require choosing a distant endpoint such as C2500. If such a distant cell is referenced, the size of the sheet becomes huge and calculations or file operations slow way down.

As each sheet is for a particular account it would be possible with such a balance cell to post the current balance of each account to another sheet. Maybe, since this is the larger goal, there is a completely different way to achieve it.

Any suggestions or even information that this is impossible, would be much appreciated.

The Gnumeric spreadsheet supports A:A for entire column and 1:1 for entire row like Excel does. In Calc you've got to use A1:A65536.

Droplet wrote:Thanks for any assistance on this. Have done fair amount of searching but could not find an answer. Using OO version 2.4.1 with Linux Mint (Ubuntu 8.04).

What if one wants to operate on ALL the current and subsequent values in a column? Normally one specifies a range (C1:C201). I would like to specify C*, as if "*" meant all values in column C.

Situation: Spreadsheet is serving as a day book. Each time there is a transaction, it is entered on a row under columns for TRANSACTION (A), DATE (B), FUNDS IN (C), FUNDS OUT (D), and BALANCE (E). The formula in cell E3, for example, is =E2+C3-D3. Cells in columns C, D, and E are formatted for currency.

Objective is to have a cell at the top (above where this data accumulates) which gives the current balance. This could be achieved if a formula could be written which subtracted the totals of column C from the totals of column D.

Even better would be to operate on all cells of a column below a specified cell.

The sheet is constantly receiving new transactions so a limited range of cells in a column is either going to be rendered invalid when the bottom of the range is passed or require choosing a distant endpoint such as C2500. If such a distant cell is referenced, the size of the sheet becomes huge and calculations or file operations slow way down.

As each sheet is for a particular account it would be possible with such a balance cell to post the current balance of each account to another sheet. Maybe, since this is the larger goal, there is a completely different way to achieve it.

Any suggestions or even information that this is impossible, would be much appreciated.

Just select the entire column and give it a data range name DATA > DEFINE NAME > etc
For smaller ranges again define the appropriate range.

You're probably better off using a database for anything with this much data.

Villeroy wrote:The Gnumeric spreadsheet supports A:A for entire column and 1:1 for entire row like Excel does. In Calc you've got to use A1:A65536.

Thank you! Your suggestion worked.

Droplet wrote:

Villeroy wrote:The Gnumeric spreadsheet supports A:A for entire column and 1:1 for entire row like Excel does. In Calc you've got to use A1:A65536.

Thank you! Your suggestion worked.

OK, keep in mind that A1:A65536 is not exactly the same as A:A. SUM(A1:A65536) will shrink when you delete cells (or entire rows).
OFFSET($A$1;0;0;65536;1) returns a reference to A1, 0 rows offset, 0 columns offset, resized to 65536 rows and 1 row. That range gets it's dimensions calculated from constant values and will never change when you remove cells.

Thanks to Villeroy for the amplification. This is good to know.

Villeroy wrote:OK, keep in mind that A1:A65536 is not exactly the same as A:A. SUM(A1:A65536) will shrink when you delete cells (or entire rows).
OFFSET($A$1;0;0;65536;1) returns a reference to A1, 0 rows offset, 0 columns offset, resized to 65536 rows and 1 row. That range gets it's dimensions calculated from constant values and will never change when you remove cells.

Thank you, OFFSET() seems to be working.
Though, I would prefer A:B notation, as more familar to many users of M$ Office suite.

I've got hit with a task when I need to repeatedly copy-paste-delete-sort certain block of data, and make represenation of it on separate sheet.
I've used VLOOKUP(), but had a hard time defining range which would not run back and forth.