ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Row references (https://www.excelbanter.com/excel-worksheet-functions/270870-row-references.html)

jeffrey

Row references
 
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

Ron Rosenfeld[_2_]

Row references
 
On Sun, 24 Jul 2011 20:16:10 -0700 (PDT), jeffrey wrote:

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



=SUM(INDIRECT(ADDRESS(B1,1,,,"sheet1")&":"&ADDRESS (C1,1)))



All times are GMT +1. The time now is 06:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com