[Issue] Using a variable for a cell reference

Discuss the spreadsheet application

[Issue] Using a variable for a cell reference

Postby Rienk » Sat Jan 09, 2010 4:36 am

I would like to be able to insert a "variable" into a cell reference.
I have a list/table on a sheet; and would like to reference that sheet in various cells, based on the list.
For example, I would like to enter into a cell, something to the effect of " =Cx " , where the 'x' is another cell value (any integer), and of course, 'C' is the column.
Is it also possible to have something like this within a more complicated formula?

Sorry if this has been asked before, but I have had no success finding this, either in OO-help or online.
Thanks in advance, Rienk
Last edited by Villeroy on Sun Jan 10, 2010 1:15 pm, edited 1 time in total.
OpenOffice 3.1 on Windows XP
Rienk
 
Posts: 3
Joined: Sat Jan 09, 2010 3:02 am

Re: Using a variable for a cell reference

Postby ken johnson » Sat Jan 09, 2010 4:46 am

Can be done using INDIRECT, OFFSET or INDEX.
If A1 holds the x value you referred to...
=INDIRECT("C"&A1)
or
=OFFSET($C$1;A1-1;0)
or
=INDEX($C$1:$C$65536;A1)

Ken Johnson
AOO 4.1.1 on Ms Windows 7
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
ken johnson
Volunteer
 
Posts: 846
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: Using a variable for a cell reference

Postby Rienk » Sun Jan 10, 2010 8:40 am

Thanks so much Ken; I assume such can similarly be used from other sheets ( $'Sheet2'.Cx ). Can these also be used for cells from other documents?
Your help is much appreciated. RA
OpenOffice 3.1 on Windows XP
Rienk
 
Posts: 3
Joined: Sat Jan 09, 2010 3:02 am

Re: Using a variable for a cell reference

Postby ken johnson » Sun Jan 10, 2010 10:31 am

I gave them all a shot but only succeeded with INDEX.
I used a doc saved as Source.ods and another saved as Destination.ods with this formula in Sheet1.B1 of Destination.ods...
=INDEX('file:///C:/Documents and Settings/Admin/My Documents/Source.ods'#$Sheet1.A1:A9;A1)
so the value in A1 on the same sheet controlled which cell from 'Source.ods'#$Sheet1.A1:A9 had its value returned in 'Destination.ods'#$Sheet1.B1.
Source.ods had to be saved and the Link to it updated plus it could be either opened or closed.

I may have been doing something wrong with OFFSET and INDIRECT. I'll need to look into that.

Ken Johnson
AOO 4.1.1 on Ms Windows 7
Help improve this forum by clicking the EDIT button on your first post then adding "[Solved]" to its heading once a solution to your problem has been provided.
ken johnson
Volunteer
 
Posts: 846
Joined: Sun May 31, 2009 1:35 am
Location: Sydney, Australia

Re: Using a variable for a cell reference

Postby Villeroy » Sun Jan 10, 2010 10:45 am

/tmp/source.ods'#$Sheet1.$A$1:$C$10 filled with address(row();column()) returning the respective cell addresses
This sheet's A1 and B1 having 2 and 3 (row and column).

This is a bug, I think:
=OFFSET('file:///tmp/source.ods'#$Sheet1.$A$1 ; $A$1-1 ; $B$1-1) ==> Err.504
more explicit:
=OFFSET('file:///tmp/source.ods'#$Sheet1.$A$1 ; $A$1-1 ; $B$1-1 ; 1 ; 1) ==> Err.504
http://qa.openoffice.org/issues/show_bug.cgi?id=108198

This works well:
=INDEX('file:///tmp/source.ods'#$Sheet1.$A$1:$C$10 ; $A$1 ; $B$1) ==> C2
and this: =DDE("soffice";"/tmp/source.ods";"$Sheet1."&ADDRESS($A$1;$B$1)) ==> C2
 Edit: I forgot this: 
=INDIRECT("'file:///tmp/source.ods'#$Sheet1."&ADDRESS($A$1;$B$1))
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 19240
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] Using a variable for a cell reference

Postby Rienk » Sun Jan 10, 2010 10:12 pm

I'm trying to get it to work with a cell from the same document, but a different sheet.
I can get indirect to work, such as:

=INDIRECT("C"&A1+4) [this is in cell C1 off of the same sheet, which is called "Contact_List"]

and then:

=Contact_List.C1

Is used to pull it onto another page.
However, I really want to be able to import this "Contact List" sheet from another document; thus, having the first line on the same page does not allow this.
But if I can somehow get the first equation (or one like it) to pull info off of a different sheet while still using a variable - this would be great (and what I need to accomplish).
All help is appreciated.

OKAY, FOR WHAT IT'S WORTH, I FIGURED IT OUT.
The appropriate syntax is...

=INDIRECT(ADDRESS(A1;B1;1;;"Other_Sheet"))

Where A1 is the row referred to, B1 is the column, and "Other_Sheet" is the name of the sheet you are grabbing from.
If cell A1 had "2" in it, and cell B1 had "4" in it, this would call up cell 'D2' from the sheet "Other_Sheet". Without the "indirect" function, the cell would literally say "Other_Sheet.$D$2". To get the Value of that cell, you need to use the 'Indirect' function along with the 'Address' function, as shown.

You can also subract/add row or column offsets, simply by adding numbers. For example...

=INDIRECT(ADDRESS(A1+3;B1-2;1;;"Other_Sheet"))

Thus, A1=2 and B1=4 would return "2+3" and "4-2" (or 5 and 2), which would equate to cell 'B5' on "Other_Sheet".
Be aware that you don't have to use both variables (i.e. - A1 and B2). You could simply put in a number that represents the row or column (3 would represent either row "three" or column "C", depending on which variable you wanted to be set).

=INDIRECT(ADDRESS(5;2;1;;"Other_Sheet"))

This would also return cell 'B5'.
(FYI, the other variables listed, the "1" and the blank space, are (somewhat) explained in OO Help).
I hope this helps others!
OpenOffice 3.1 on Windows XP
Rienk
 
Posts: 3
Joined: Sat Jan 09, 2010 3:02 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests