Results 1 to 3 of 3

20090121, 16:31 #1
 Join Date
 Jan 2007
 Posts
 123
 Thanks
 13
 Thanked 1 Time in 1 Post
Using INDIRECT to specify how many cells to sum (Excel 2003)
I have annual data in a horizontal format (years goiing across the top). I would like to write a formula which will sum X number of previous cells, where X is specified in a cell somewhere.
I've heard this can be done with INDIRECT, but the Excel help file on this left me confused.
I've attached an example, with the following query (The query will only make sense when you look at the example):
In Cell R27, what formula, using the INDIRECT function, sums X number of years (columns), ending with the present year (2006), when the number of years to to add is specified in cell O19?
In other words, if O19 contains a 5, how I can I, in cell R27, sum cells N26:R26?
Also  is there a way to make this formula easily copyable across, so that, say, I could copy it to cell S27, and it would  when O19 contains a 5  sum O26:S26?
Any help would be appreicated...

20090121, 16:52 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Using INDIRECT to specify how many cells to sum (Excel 2003)
I'd use OFFSET:
=SUM(OFFSET(R26,0,1$O$19,1,$O$19))
This formula can be copied across.
The syntax for OFFSET is OFFSET(range, row_offset, column_offset, height, width)

20090121, 16:57 #3
 Join Date
 Jan 2007
 Posts
 123
 Thanks
 13
 Thanked 1 Time in 1 Post
Re: Using INDIRECT to specify how many cells to sum (Excel 2003)
Hans saves the day again!
Promise me you'll never retire ... Promise! PROMISE!!!