10

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.)

Anirvan
  • 6,214
  • 5
  • 39
  • 53

3 Answers3

12

I had the same need--looks like the OFFSET function will let you do that.

So for the above:

=SUM(OFFSET(A1,0,1,1,3))

to break it down:

OFFSET(reference cell,
       row offset from ref cell to start the range,
       col offset to start the range, height of range you want,
       width of range you want)

you can make the offsets zero if you want, or + to ref down, - to ref up

nhahtdh
  • 55,989
  • 15
  • 126
  • 162
user2347836
  • 136
  • 1
  • 3
3

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))
chris neilsen
  • 52,446
  • 10
  • 84
  • 123
  • 2
    `INDIRECT` must be avoided whenever possible. It is volatile, that is, recalculates each time any cell changes. – GSerg Jun 04 '11 at 00:21
2

Not sure what you want.
Did you mean this?

dim parent_range as range
dim child_range as range

set parent_range = me.range("a1:e2")
set child_range = range(parent_range.rows(1).cells(2), parent_range.rows(1).cells(4))

msgbox "=sum(" & child_range.address(false, false, xla1) & ")"

Or did you want it as a formula?

=SUM(INDEX($A$1:$E$2,1,2):INDEX($A$1:$E$2,1,4))
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • Thanks for your help, GSerg. Unfortunately, I'm using Microsoft Excel 2011 for Mac, which doesn't support Excel VBA macros. I guess I'm looking for a pure-Excel solution. – Anirvan Jun 03 '11 at 23:56
  • @Anirvan Try the formula then. – GSerg Jun 03 '11 at 23:57
  • 1
    That's strange I've developed macros for Excel 2011 for Mac there are a few quirks compared to Windows but for the most part everything was the same. I would be surprised if you can't use this in Excel 2011 for mac. – David Zemens May 03 '13 at 17:34