setting a range

[bernd]

Hello,

Is it possible to set a range by referring to the column numbers?

I want the SUM of row 3 for the columns A to D

Normally this is =SUM(A33)

I would like the user to put in a value in some other cell that sets
the range of the formula. So for a range of column a A to D the user
types a 1 and a 4 in two other cells.

Anyone?


Bernd

 

[Pete_UK]

If it is only going to be a simple range up to column Z and always on
row 3, then you could make use of this:

put the number of the first column in A2 (i.e. 1) and the number of
the second column in B2 (i.e. 4) and then this formula where you want
it (eg C2):

=IF(OR(A2<1,B2<1,A2>26,B2>26),"not
valid",SUM(INDIRECT(CHAR(64+A2)&"3:"&CHAR(64+B2)&"3")))

This will test for invalid numbers in A2 and B2.

Hope this helps.

Pete