[Solved] Cell reference with another cell value?

[Hagar Delest]

Hello,

I have been trying to figure out, without any success, if you can reference a cell in a formula, with the value of another cell as the referenced cell row number. As in:

=SUM(B1:Bvalue of cell D1)

Is this even possible, or do I have to go through a long workaround...
Thanks!

[FJCC]

Something like

Code: Select all   Expand viewCollapse view

=SUM(OFFSET(B1;0;0;D1))

should work. Check out the help on the OFFSET() function and see if that makes sense.

[MrFixitND]

Hmm, that's interesting, but not particularly useful for what I am trying to do. That would certainly allow me to do what I intend, IF I was always starting at B1, and not say B20. (Or else I'd have to drop the value of D1 by 20 to get the result I wanted then, due to OFFSET being a relative count, basically...)

Maybe another way to phrase my question is this: I am trying to make a =SUM that excludes a certain range of data, where I want to be able to define that range in separate cells. Then it must also be copy-able to another cell, for relative use. Like this:

=SUM(B1:B100; -Bvariable defined in $D$1:BVariable defined in $D$2)
or
=SUM(B1:Bvariable defined in $D$1; BVariable defined in $D$2:B100)

Does that make sense?

[MrFixitND]

Well, I found a workaround that works for me, thought I'd share it. In order to make this work, I had to make a column (F) in my dataset which simply had an incrementing number, that is the same as the row number. Then I simply used a SUMIF with a comparator to my variable boxes like this:

=SUM(SUMIF(F1:F100;"<"&D1;B1:B100);SUMIF(F1:F100;">"&D2;B1:B100)

Still would like to know if there is a way to use a variable to directly replace the reference row number... (As in B"value of D1")

[acknak]

... I am trying to make a =SUM that excludes a certain range of data, where I want to be able to define that range in separate cells.

Maybe
=SUM(B1:B100)-SUM(OFFSET($B$1;$D$1;0;$D$2;1)

This expects D1, D2 to be the offset to the starting row, and the height in rows, not the start/end row, you'll have to make adjustment for that.

[keme]

You can build the address as a text string, then use INDIRECT() to make a reference from that string. This approach will easily break portability, and may also require caution when you insert/remove rows/columns in your spreadsheet, as references are not automatically updated inside string constants.

This may be a starting point for what you try to do:

Code: Select all   Expand viewCollapse view

=SUM(INDIRECT("B"&$D$1&":B"&$D$2))

For many situations it is better to use the ADDRESS() function to create the starting address/range, then SUBSTITUTE() to insert the desired row numbers before conversion by INDIRECT(). This helps with portability when you have multiple sheets (tabs) in your file. (The syntax for sheet references is slightly different between different spreadsheet applications.) With this approach, consider having the generated address string in a separate cell (which can be hidden). Breaking your formula down to smaller steps makes debugging easier.

Note that using OFFSET(), perhaps also with named ranges, will normally be much easier to work with than INDIRECT constructs. (More compact, and also easier to understand in a year when you try to edit your spreadsheet formulas, having forgotten the details about what you were doing.) Trust me. I've gone there both ways.

[RobtAndr]

=SUM(INDIRECT("E"&$G$5&":E"&$H$5)) was what I've been looking for.
Here is my base formula =SUM(E60:E67)
I have a spreadsheet with Daily CoVid numbers, (Rows ## through ####) so each day represents a row of data
I have a summary section at the top with a summary string that references numbers (incremented by seven) that will point me to a set of cells for the summarized week.
I've been looking for days and found this. Thanks so much for the reply.
Robert Andrews
Arizona (Rated by NY Times as currently the #1 place in world for CoVid growth) YIKES