Forum Discussion
Cell references
Hi everyone! I want to know if there is a way to have a cell reference like this: In my workbook, I use a formula to add a range of cells (eg. "SUM(C1:C40)"). Suppose I constantly change the numbers (eg. sometimes I want to add C5:C37), so what I WOULD LIKE to do is, say, type the cell numbers in different cells (eg. in my example, I would type the number "5" in, say, Cell A1, and the number "37" in, say, Cell A2), and Excel should figure out that I want to add C5:C37. Something to the tune of, to have a cell reference like "SUM(C[a1{which, in my example, would be "5"}]:C[a2(which in my example, would be "37"}]. In other words, if I can just type the cell number which I want to refer to, and Excel will "pick up" on it, that would be great for me.
2 Replies
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) )
