Forum Discussion
Cell references
Yes there are a few ways to this:
The function INDIRECT() specifically converts a string input into a cell reference so the following should work
=SUM(INDIRECT("C"& A1 & ":C" & A2))
but you should be careful using INDIRECT as it is not managed by EXCEL. so if you insert rows or copy and paste or anything like that and expect EXCEL to adjust your formula, it will NOT because it doesn't know what range you are actually referencing. Furthermore, Excel will recalculate on EVERY change on the worksheet instead of only if that change might affect this formula, again because Excel doesn't know what range is being used.
Other options include OFFSET or INDEX functions. so for example:
=SUM(INDEX(C:C,A1):INDEX(C:C,A2))
=SUM(OFFSET(C1, A1-1, , A2-A1+1))
although these will probably still get calculated all the time it may 'behave' slightly better. For example if you insert a column to the left of A (i.e. create a new column A for additional info) then the latter formulas should natually shift accordingly while using INDIRECT would not.
Hope that helps
Yes there are a few ways to this:
The function INDIRECT() specifically converts a string input into a cell reference so the following should work
=SUM(INDIRECT("C"& A1 & ":C" & A2))
but you should be careful using INDIRECT as it is not managed by EXCEL. so if you insert rows or copy and paste or anything like that and expect EXCEL to adjust your formula, it will NOT because it doesn't know what range you are actually referencing. Furthermore, Excel will recalculate on EVERY change on the worksheet instead of only if that change might affect this formula, again because Excel doesn't know what range is being used.
Other options include OFFSET or INDEX functions. so for example:
=SUM(INDEX(C:C,A1):INDEX(C:C,A2))
=SUM(OFFSET(C1, A1-1, , A2-A1+1))
although these will probably still get calculated all the time it may 'behave' slightly better. For example if you insert a column to the left of A (i.e. create a new column A for additional info) then the latter formulas should natually shift accordingly while using INDIRECT would not.
Hope that helps
Not very practical, just in case
=SUM( C:.C*( ROW(C:.C)>=A1 )*( ROW(C:.C)<=A2 ) )
=SUM( CHOOSEROWS( C:.C, SEQUENCE(A2-A1+1,,A1)))
=SUM(TAKE( DROP(C:.C, A1-1), A2-A1+1) )