[Solved] Cell reference with another cell value?

Discuss the spreadsheet application

[Solved] Cell reference with another cell value?

Postby MrFixitND » Wed Jul 12, 2017 11:08 pm

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... :-D
Thanks!
Last edited by Hagar Delest on Sun Jul 12, 2020 11:44 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.3 on Microsoft Windows 10
MrFixitND
 
Posts: 3
Joined: Wed Jul 12, 2017 11:03 pm

Re: Cell reference with another cell value?

Postby FJCC » Wed Jul 12, 2017 11:17 pm

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.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7968
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Cell reference with another cell value?

Postby MrFixitND » Wed Jul 12, 2017 11:47 pm

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?
OpenOffice 4.1.3 on Microsoft Windows 10
MrFixitND
 
Posts: 3
Joined: Wed Jul 12, 2017 11:03 pm

Re: Cell reference with another cell value?

Postby MrFixitND » Thu Jul 13, 2017 12:50 am

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... :-D (As in B"value of D1")
OpenOffice 4.1.3 on Microsoft Windows 10
MrFixitND
 
Posts: 3
Joined: Wed Jul 12, 2017 11:03 pm

Re: Cell reference with another cell value?

Postby acknak » Thu Jul 13, 2017 1:55 am

... 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.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Cell reference with another cell value?

Postby keme » Fri Jul 14, 2017 10:02 am

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.
Apache OO 3.4.1/4.1.2/4.1.3, on Ms Windows 7/10 and Mac OS-X 10.8.5 and 10.11.
LibreOffice 4.3.3.2 on Mepis Antix MX 14
User avatar
keme
Volunteer
 
Posts: 3401
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Cell reference with another cell value?

Postby RobtAndr » Sat Jul 11, 2020 9:58 pm

=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
RobtAndr Open Office 4.1.7 AOO417m1(Build:9800) - Rev. 46059c9192 Mac OS
RobtAndr
 
Posts: 2
Joined: Sat Jul 11, 2020 9:53 pm


Return to Calc

Who is online

Users browsing this forum: Google [Bot], mike.stirton and 8 guests