How do I specify a variable Excel range?

Question

I'd like to be able to dynamically specify a range in Excel, based on row/column variables.

Let's say I have a file with contents that look sort of like this:

   A   B   C   D   E
1  10  11  12  13  14
2  51  52  53  54  55

If I wanted to sum the items in row 1, columns 2-4 (i.e. 11 + 12 + 13), how would I specify that?

If I were doing it by hand, I'd type:

=SUM(B1:D1)

...but how do I programatically generate that range definition on the fly, knowing only the wanted row (1) and column numbers (2-4)?

=SUM(????)

Thanks in advance for your help!

(I'm using Microsoft Excel 2011 for Mac, so Excel VBA/macro-based solutions won't work for me.)

Answer 1

It depends on how the "known" row and column numbers are referenced
For example if they are values in cells on the sheet:

     A          B
 9   Row        1
10   ColStart   1
11   ColEnd     4

Use a the INDRECT function to build a range reference

=SUM(INDIRECT("R"&B9&"C"&B10&":R"&B9&"C"&B11,FALSE))