[Solved] May a range be an entire column?

Discuss the spreadsheet application

[Solved] May a range be an entire column?

Postby Droplet » Mon Sep 08, 2008 12:47 pm

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.
Last edited by Droplet on Tue Sep 09, 2008 12:08 pm, edited 1 time in total.
Droplet
 
Posts: 3
Joined: Mon Sep 08, 2008 12:21 pm

Re: May a range be an entire column?

Postby Villeroy » Mon Sep 08, 2008 1:40 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: May a range be an entire column?

Postby jrkrideau » Mon Sep 08, 2008 3:43 pm

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.
OpenOffice 4.1.6; Ubuntu 18.04
jrkrideau
Volunteer
 
Posts: 3748
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: May a range be an entire column?

Postby Droplet » Tue Sep 09, 2008 12:10 pm

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.
OOo 2.4.X on Ubuntu 8.x
Droplet
 
Posts: 3
Joined: Mon Sep 08, 2008 12:21 pm

Re: May a range be an entire column?

Postby Villeroy » Tue Sep 09, 2008 1:29 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27760
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] May a range be an entire column?

Postby Droplet » Tue Sep 09, 2008 7:28 pm

Thanks to Villeroy for the amplification. This is good to know.
OOo 2.4.X on Ubuntu 8.x
Droplet
 
Posts: 3
Joined: Mon Sep 08, 2008 12:21 pm

Re: May a range be an entire column?

Postby AnrDaemon » Sun Jul 29, 2012 6:51 am

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.
AOo 3.4.1 on Ms Windows XP/ OOo 3.3.0 on Mac OS X 10.7
AnrDaemon
 
Posts: 23
Joined: Fri Mar 27, 2009 6:53 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 12 guests