Thanks Harlan, that is exactly what I was looking for. I figured some type
of R1C1 reference might be the way, but had only used it in VBA before. Your
example will be very helpfull.
"Harlan Grove" wrote:
> Quizarate wrote...
> >I'd like to create a non VBA based formula that will sum up a range that is
> >offset from the activecell. I tried the following formula to sum a four cell
> >range:
> >
> >=SUM(OFFSET(INDIRECT(CELL("address")),-4,0,4,1))
> >
> >This works fine when I initially enter it, but when numbers in the sum range
> >change the formula result changes to 0.
> >
> >Anyone have any ideas of how I can get around this?
>
> Let's say you had {1;2;3;4} in B2:B5 and {9;8;7;6} in C2:C5. If you
> enter your formula above in B6, while B6 is the active cell your
> formula would return the same result as =SUM(B2:B5). However, if you
> move to cell C6 and do anthing that triggers recalculation, your
> formula in B6 would return the same result as =SUM(C2:C5).
>
> If you always want the sum of the range of 4 cells immediately above
> the cell containing the formula, the simplest way would be to use
> INDIRECT with R1C1 references, e.g.,
>
> =SUM(INDIRECT("R[-4]C:R[-1]C",0))
>
>
Bookmarks