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!
[Solved] Cell reference with another cell value?
[Solved] Cell reference with another cell value?
Last edited by Hagar Delest on Sun Jul 12, 2020 11:44 am, edited 1 time in total.
Reason: tagged solved
Reason: tagged solved
OpenOffice 4.1.3 on Microsoft Windows 10
Re: Cell reference with another cell value?
Something like
should work. Check out the help on the OFFSET() function and see if that makes sense.
Code: Select all
=SUM(OFFSET(B1;0;0;D1))
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Cell reference with another cell value?
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?
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?
OpenOffice 4.1.3 on Microsoft Windows 10
Re: Cell reference with another cell value?
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")
=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")
OpenOffice 4.1.3 on Microsoft Windows 10
Re: Cell reference with another cell value?
Maybe... 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.
=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.
AOO4/LO5 • Linux • Fedora 23
Re: Cell reference with another cell value?
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:
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.
This may be a starting point for what you try to do:
Code: Select all
=SUM(INDIRECT("B"&$D$1&":B"&$D$2))
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.
Apache OO 4.1.12 and LibreOffice 7.5, mostly on Ms Windows 10
Re: Cell reference with another cell value?
=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
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
RobtAndr Open Office 4.1.7 AOO417m1(Build:9800) - Rev. 46059c9192 Mac OS
-
- Posts: 2
- Joined: Tue Apr 23, 2019 12:54 pm
Re: [Solved] Cell reference with another cell value?
FYI:
If you just need to reference a single cell, you can use the following simplified syntax:
=INDIRECT("G5")
or for specific cell in another sheet:
=INDIRECT("MySheetName.B3")
A downside to using INDIRECT is that if you move the target field, the reference ID doesn't change.
Also if you drag the cell, the reference values don't increment.
This negative side-effect can be used to avoid incrementing specific cell references, while allowing other cell references to auto increment when dragging the cell(s).
=CONCATENATE(INDIRECT("FixedPosVal.B3");" ";Keywords.B2)
If you just need to reference a single cell, you can use the following simplified syntax:
=INDIRECT("G5")
or for specific cell in another sheet:
=INDIRECT("MySheetName.B3")
A downside to using INDIRECT is that if you move the target field, the reference ID doesn't change.
Also if you drag the cell, the reference values don't increment.
This negative side-effect can be used to avoid incrementing specific cell references, while allowing other cell references to auto increment when dragging the cell(s).
=CONCATENATE(INDIRECT("FixedPosVal.B3");" ";Keywords.B2)
OpenOffice 3.1