How to do without using INDIRECT?

[obiron]

Hi guys, I have a cell (lets call is $D$32) which holds an integer .

in cell $D$33 I want to put the sum of row 14 for n columns to the right of column D.

E.g.
If $D$32 = 3 then I want to SUM(E$14:$G$14)
if $D$32 = 6 then I want to SUM($E$14:$J$14)

I am currently using INDIRECT and OFFSET which is A) ugly and B) volatile

= SUM(E14:INDIRECT(CELL("address",OFFSET(E14,0,D32-1,1,1))))

There are 52 of these calculations on the worksheet and I need to update the values up to 30,000 times so avoiding volatile calculations would be nice. Can you think of another way of doing it without using VBA?

TIA

Obiron

 

[MrKowz]

Try:

=SUM(E14:INDEX(14:14,,$D$32+4))

 

[obiron]

Genius!

slight modification

=SUM(E14:INDEX(14:14,,D32+COLUMN(D32)))

This way when you copy the formula across the row the D32 and column(D32) updates to the reference cell and returns the column number