Ampersands and formulae

[geewhysee]

Hi all,

This may be too general of a question but I am trying to understand the syntax for chopping together multiple references within a formula using ampersands. I'll give a quick random example.

I have a list in column A and I want to sum some portion of that list. The portion I want to sum is dependant on a dynamic number that is in cell B2. My attempt at this is

="=sum(A1:A"&B1&")"

but that doesn't seem to resolve correctly. It just prints =sum(A1:A6) in the cell. How do I get the formula to resolve? This is a general question so I am not interested in other ways of solving this specific problem, only if it is possible to use & in this way within a formula and if so what am I getting wrong?

Thanks

 

[Jonmo1]

Try

=SUM(A1:INDEX(A:A,B1))

 

[dave3009]

I think you may need to use INDIRECT if it's directly on a sheet.

=SUM(INDIRECT("A1:A"&B1))

 

[geewhysee]

I think you may need to use INDIRECT if it's directly on a sheet.

=SUM(INDIRECT("A1:A"&B1))

This is perfect, Thank you.