[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
novocaine
Newbie

Joined: 18 Apr 2009
Posts: 2

 Posted: Sat Apr 18, 2009 8:02 pm    Post subject: Using a cell value as a row number 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.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Sun Apr 19, 2009 1:38 am    Post subject: =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
davidh182

Joined: 01 Apr 2004
Posts: 413

Posted: Sun Apr 19, 2009 1:38 am    Post subject:

 Code: =SUM(INDIRECT("C1:C"&A1))
novocaine
Newbie

Joined: 18 Apr 2009
Posts: 2

 Posted: Sun Apr 19, 2009 7:42 am    Post subject: Thanks! The offset function is exactly what I was looking for. This is a huge help.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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