Inverted Indirect?

[gsc_81]

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.

 

[shades]

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.
________
Hash Honey Oil

 

[gsc_81]

Varied Summations

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.


View attachment SporadicSum.zip

 

[Brianwarnock]

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

 

[gsc_81]

Halleluyah!!

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!