OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Using a cell value as a row number

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
novocaine
Newbie
Newbie


Joined: 18 Apr 2009
Posts: 2

PostPosted: Sat Apr 18, 2009 8:02 pm    Post subject: Using a cell value as a row number Reply with quote

Hi,

I've been trying to figure out a way to use a cell value in a formula to identify another cell.

I have a cell where I want to keep a static value that I can update. We'll call it cell A1, with 20 as the value.

I want to use the number in A1 to define a range of cells in a formula. It would ideally be something like =sum(c10:c[A1]), where [A1] is now 20, so right now the formula would be =sum(c10:c20). I'd like to be able to change the value in A1 to 40 and have the formula automatically update to =sum(c10:c40).

I would really appreciate some help with this, because I haven't been able to find anything.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Apr 19, 2009 1:38 am    Post subject: Reply with quote

=SUM(OFFSET(C10;0;0;A1;1))
C10
0 rows offset
0 columns offset
resized to A1 rows and 1 column
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
davidh182
OOo Advocate
OOo Advocate


Joined: 01 Apr 2004
Posts: 413

PostPosted: Sun Apr 19, 2009 1:38 am    Post subject: Reply with quote

Code:

=SUM(INDIRECT("C1:C"&A1))
Back to top
View user's profile Send private message Send e-mail
novocaine
Newbie
Newbie


Joined: 18 Apr 2009
Posts: 2

PostPosted: Sun Apr 19, 2009 7:42 am    Post subject: Reply with quote

Thanks! The offset function is exactly what I was looking for. This is a huge help.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group