range specified by the value in another cell

[Zsolt Feleki]

I have the following problem:

I have let say 100 numbers in column A, and a number
n<100 in cell B1. I want to have the sum of the first
n numbers in column A, so I would need to specifiy the range
A1:An, but I need this independently of the actual value of n.
I have tried A1:A(B1) with but it doesn't work.
So has somebody any idea

 

[Arvi Laanemets]

Hi

2 ways for it

1. Use the formula
=SUM(INDIRECT("A1:A"&B1))

2. Create a named range
RangeToSum=OFFSET(SheetName$A$1,,,SheetName!$B$1,1)
The formula will be
=SUM(RangeToSum)