Thanks:  0
Likes:  0

1. Is it possible to use an array where the variable is a cell reference? This is what I have so far, which is using nested IF statements instead...

=IF(A2=1,D2,IF(A2=3,SUM(D2:F2),IF(A2=6,SUM(D2:I2),IF(A2=9,SUM(D2:L2),IF(A2=12,SUM(D2:O2),IF(A2=15,SUM(D2:R2),"X"))))))

Ideally I would like to refer to the number in A2 and sum that many cells, I have 60 columns of data!

Any ideas???

[ This Message was edited by: Peter Adey on 2002-05-08 05:20 ]

2. The short answer is yes, but check out
http://www.cpearson.com/excel/array.htm

The only thing is the ranges must be the same.

3. On 2002-05-08 05:19, Peter Adey wrote:
Is it possible to use an array where the variable is a cell reference? This is what I have so far, which is using nested IF statements instead...

=IF(A2=1,D2,IF(A2=3,SUM(D2:F2),IF(A2=6,SUM(D2:I2),IF(A2=9,SUM(D2:L2),IF(A2=12,SUM(D2:O2),IF(A2=15,SUM(D2:R2),"X"))))))

Ideally I would like to refer to the number in A2 and sum that many cells, I have 60 columns of data!

Any ideas???

[ This Message was edited by: Peter Adey on 2002-05-08 05:20 ]
I find the Indirect function useful in cases like this. Try:

=SUM(INDIRECT("D2:D"&(A2+1)))

Regards,

Pete.

4. On 2002-05-08 05:19, Peter Adey wrote:
Is it possible to use an array where the variable is a cell reference? This is what I have so far, which is using nested IF statements instead...

=IF(A2=1,D2,IF(A2=3,SUM(D2:F2),IF(A2=6,SUM(D2:I2),IF(A2=9,SUM(D2:L2),IF(A2=12,SUM(D2:O2),IF(A2=15,SUM(D2:R2),"X"))))))

Ideally I would like to refer to the number in A2 and sum that many cells, I have 60 columns of data!

Any ideas???

[ This Message was edited by: Peter Adey on 2002-05-08 05:20 ]
No need fo an array formula.

=SUM(OFFSET(D2,0,0,1,A2))

will suffice.

Or, with control on A2,

=IF(AND(A2>=1,A2<=15),SUM(OFFSET(D2,0,0,1,A2)),"X")

5. Thanks for the quick reply Rocky

The problem is I don't want sum up the values = A2 . I want to total the number of cells = A2. So,

IF a2= 9 then sum 9 cells etc, etc

any ideas???

6. Hi Peter,
Well I for one am still a tad bit confused. I will try to restate your problem and you correct me if necessary.

A2 has a number from 1 to 10 and column B has a list of amounts from \$1.00 to \$10.00. If A2=3 then you want to return \$6.00.

I think Aldin was right on track. Consider:

=SUM(B1:OFFSET(B1,A2,0))

7. On 2002-05-08 06:05, Peter Adey wrote:
Thanks for the quick reply Rocky

The problem is I don't want sum up the values = A2 . I want to total the number of cells = A2. So,

IF a2= 9 then sum 9 cells etc, etc

any ideas???
Try what I suggested.

8. many thanks to you all. the offset function works a dream ;0)

Cheers

Peter

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•