Need a REFERENCE as an output

  • Thread starter Thread starter ar001f
  • Start date Start date
A

ar001f

I would like to use a SUM(A1:A10) formula, but instead of using A1 an
A10 as range limits I would like to have two cells in a worksheet wher
I can specify the range dynamically (myself), say change it to A4:A
without actually re-editing the SUM formula cell.

Is such a thing even possible? I thought there would be a formula fo
that in Excel but could not find anything.

thanking for any kind of assistance,

--alex r
 
Assume you want to sum in column A, lower boundary is in C1 and upper in D1,
I would personally use

=SUM(INDEX(A:A,C1):INDEX(A:A,D1))

but you can also use

=SUM(INDIRECT("A"&C1&":"&"A"&D1))

or

=SUM(OFFSET($A$1,$C$1-1,,D1-C1+1,))

why? Because the first formula is non-volatile the other 2 are not.

So for A4:A6 put 4 in C1 and 6 in D1

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
You can use the INDIRECT worksheet function. So:

=SUM(INDIRECT(A1):INDIRECT(A2)) where A1 and A2 each hold a single cell
reference, or
=SUM(INDIRECT(A1)) where A1 holds a range reference (A4:A6, for example).
 
Peo Sjoblom and DDM -- you dudes (or dudets:) are awesome!

Thank you so much for the excellent and timely solutions to m
problem.

--ar
 
Back
Top