Row references

[jeffrey]

I have the following columns.

A B C D
1 1 6 D1
2 7 23 D2
3 24 31 D3
4 32 38 D4
5 39 44 D5
etc.

Columns B and C refer to the row numbers on another worksheet.
D1-D5 are array formulas. I want D1 = FUNCTION(sheet1!A1:A6). D2 =
FUNCTION(sheet1!A7:A23). D3=FUNCTION(sheet1!A24:A31). You get the
idea. Function could be sum, average, whatever that has an array
reference.

How do you do this? I hope there is an easy way of incorporating
this. Thanks for your help,

Jeff

 

[BDeitrick]

Two easy ways:
=FUNCTION(INDIRECT("Sheet1!A" & B1 & ":A" & C1))
Like
=SUM(INDIRECT("Sheet1!A" & B1 & ":A" & C1))

Or this, which I prefer because it updates as the sheet name changes:

=FUNCTION(OFFSET(Sheet1!A1,B1-1,0,C1-B1+1,1))
Like
=SUM(OFFSET(Sheet1!A1,B1-1,0,C1-B1+1,1))

Bernie