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.
[Solved] May a range be an entire column?
[Solved] May a range be an entire column?
Last edited by Droplet on Tue Sep 09, 2008 12:08 pm, edited 1 time in total.
Re: May a range be an entire column?
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: May a range be an entire column?
Just select the entire column and give it a data range name DATA > DEFINE NAME > etcDroplet 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.
For smaller ranges again define the appropriate range.
You're probably better off using a database for anything with this much data.
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: May a range be an entire column?
Thank you! Your suggestion worked.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.
OOo 2.4.X on Ubuntu 8.x
Re: May a range be an entire column?
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).Droplet wrote:Thank you! Your suggestion worked.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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] May a range be an entire column?
Thanks to Villeroy for the amplification. This is good to know.
OOo 2.4.X on Ubuntu 8.x
Re: May a range be an entire column?
Thank you, OFFSET() seems to be working.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.
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.
AOo 3.4.1 on Ms Windows XP/ OOo 3.3.0 on Mac OS X 10.7