Excel INDIRECT or OFFSET for variable Sum range

Question

I have a sheet with 55 "blocks" of data, stacked vertically between cells A1 through AO44000. All blocks are the same size - each block has 865 rows and 41 columns.

In column AK I currently have formula =SUM(H14:O14). This formula is repeated for all rows in each block. I need column “H” and column “O” to be variable for each block based on input from another sheet.

I have tried using OFFSET and INDIRECT but I'm having a hard time getting a formula correct. VBA will unfortunately not be an option.

Any suggestions?

Answer 1

Maybe this would at least be a start:

=SUM(INDIRECT(Sheet2!A1&ROW()&":"&Sheet2!B1&ROW()))  

where Sheet2 A1 and B1 contain the column reference letters to stand in place of H and O.