I have a spreadsheet controlled by another department from which I need to pull sums. The columns stay constant, while the row interval between the Key fields varies as does the number of records in the key fields.

Ex:
Sheet1Key
E19--------K19-----M19
------------K20
------------K21-----M21
----------------------------N22
----------------------------N23
E27--------K27-----M27
------------K28-----M28
------------K29
----------------------------N30

E39--------K39
------------K40-----M40
------------K41-----M41



----------------------------N45
E64 .. ETC




What I was wondering is if there is a way to use the value of 1 cell (say A1) to denote the row value in a formula? I could have the range length set in (B1). If I understand the indirect function this would seem to be it’s inverse.
Basically
Ksum =Sum(Sheet1!K&[A1]:Sheet1!K&([A1]+[B1]))


Hidden sheet sums
Row value-----------Range---Ksum---Msum---Nsum
=Row(Sheet1!E19)----4
=Row(Sheet1!E27)----3
=Row(Sheet1!E39)----6

This hidden sheet needs to have the sums in a table type structure as they are to be imported into SQL tables periodically.

I hope that I've explained this adequetely. At this point I don't know if it's achievable or I'm slipping into an alternate reality. Any input or suggestions of alternate possibilities is appreciated.

Howdy. It would help if you could post this in an actual Excel file and attach it (it doesn't work too well to try a setup using the board itself). That way we can provide a working solution for you.

Hey Shades, thanks for your suggestion. I finally bugged IT enough to let me upload. (very tight controls here).

In a nutshell I am trying to find a way to dynamically set the row component of a cell(or range) reference in a formula to the value of another cell.

This seems like it should be possible but I think that I've looked at it to the point of over thinking it.

Again all help (or attempts) is appreciated.


22334

Your formulas will be like
=SUM(INDIRECT("Sheet1!k"&$A2):INDIRECT("sheet1!k"&($A2)+($B2)))

But why not just have start and end of range then

=SUM(INDIRECT("Sheet1!k"&$A2):INDIRECT("Sheet1!k"&($B2)))


Brian

Thank you ever so much!!
I could have sworn that I tried that combination without success but it seems to work fine now! Thanks again.

As for using the end range I will look into it as I am trying to work it so that both parameters are calculated fields.
This is to hopefully pre-compensate for any new rows that might be inserted by the department controlling the Main Sheet.

Have a wonderful weekend!