Sum a dynamically-defined range
 

I have a series of cells containing the defining limits of a range, i.e.:
-sheet name
-top row number
-top column number
-bottom row number
-bottom column number

How do I sum (or otherwise operate on) the array so defined? I have tried
formulae containing combinations of various ADDRESS and INDIRECT functions,
but have not yet come across one that works.

Sum a dynamically-defined range
 

Try the below with

A1-sheet name
A2-top row number
A3-top column number
A4-bottom row number
A5-bottom column number


=SUM(OFFSET(INDIRECT("'" & A1 & "'!A1"),A2-1,A3-1,A4-A2+1,A5-A3+1))

--
Jacob


"Hershmab" wrote:

I have a series of cells containing the defining limits of a range, i.e.:
-sheet name
-top row number
-top column number
-bottom row number
-bottom column number

How do I sum (or otherwise operate on) the array so defined? I have tried
formulae containing combinations of various ADDRESS and INDIRECT functions,
but have not yet come across one that works.

Sum a dynamically-defined range
 

Using these references:
A1 -sheet name
a2 -top row number
a3 -top column number
a4 -bottom row number
a5 -bottom column number

This formula will genearte a summation of the dynamic range:
=SUM(OFFSET(INDIRECT("'"&A1&"'!"&ADDRESS(A2,A3)),, ,A4-A2+1,A5-A3+1))

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Hershmab" wrote:

I have a series of cells containing the defining limits of a range, i.e.:
-sheet name
-top row number
-top column number
-bottom row number
-bottom column number

How do I sum (or otherwise operate on) the array so defined? I have tried
formulae containing combinations of various ADDRESS and INDIRECT functions,
but have not yet come across one that works.

Sum a dynamically-defined range
 

=SuM(INDIRECT(A1&"!R"&A2&"C"&A3&":R"&A4&"C"&A5,FAL SE))

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Hershmab" wrote in message
...
I have a series of cells containing the defining limits of a range, i.e.:
-sheet name
-top row number
-top column number
-bottom row number
-bottom column number

How do I sum (or otherwise operate on) the array so defined? I have tried
formulae containing combinations of various ADDRESS and INDIRECT
functions,
but have not yet come across one that works.