Using a cell value as a row number

novocaine · Newbie Joined: 18 Apr 2009 Posts: 2

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 · 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 http://forum.openoffice.org

davidh182 · OOo Advocate Joined: 01 Apr 2004 Posts: 413

novocaine · Newbie Joined: 18 Apr 2009 Posts: 2

Thanks! The offset function is exactly what I was looking for. This is a huge help.