| View previous topic :: View next topic |
| Author |
Message |
SharkD
General User
Joined: 17 Sep 2008
Posts: 19
|
 Posted: Wed Aug 05, 2009 8:33 am Post subject: Substitute for INDIRECT/LOOKUP? |
 |
|
I'm using several formulas similar to the following:
| Code: | | INDIRECT("Sheet1.$B"&$X4&":$B"&$Y4) |
I'm using them inside LOOKUP functions. For instance:
| Code: | | LOOKUP($J4;INDIRECT("Sheet1.$B"&$X4&":$B"&$Y4);INDIRECT("Sheet1.$C"&$X4&":$C"&$Y4)) |
The problem is that things get messed up if I rename the sheet or if I move columns. Is there a better alternative?
Thanks!
-Mike |
|
| Back to top |
|
 |
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
Location: Kingston ON Canada
|
| Posted: Wed Aug 05, 2009 10:16 am Post subject: Re: Substitute for INDIRECT/LOOKUP? |
|
|
| SharkD wrote: | I'm using several formulas similar to the following:
| Code: | | INDIRECT("Sheet1.$B"&$X4&":$B"&$Y4) |
I'm using them inside LOOKUP functions. For instance:
| Code: | | LOOKUP($J4;INDIRECT("Sheet1.$B"&$X4&":$B"&$Y4);INDIRECT("Sheet1.$C"&$X4&":$C"&$Y4)) |
The problem is that things get messed up if I rename the sheet or if I move columns. Is there a better alternative?
Thanks!
-Mike |
Can you tell us what you are trying to achieve in substantive terms?
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2 |
|
| Back to top |
|
|
keme
Moderator
Joined: 30 Aug 2004
Posts: 2744
Location: Egersund, Norway
|
| Posted: Wed Aug 05, 2009 3:18 pm Post subject: |
|
|
=INDIRECT("Sheet1.$B"&$X4&":$B"&$Y4) defines a range from the row number stored in X4 to the row number stored in Y4. You can probably achieve exactly the same using the OFFSET() function::
=OFFSET(sheet1.B1;X4-1;0;Y4-X4+1;1) might do the job. (Depending on what behaviour you need when inserting/deleting rows/columns, you may want a different starting point, and different displacement values. Hence, jkrideau's question on what you want to achieve is still valid.)
The INDIRECT() function uses string representations of cell references, which makes the addressing "hardcoded". OFFSET() uses a real reference as the starting point, so it will update to reflect changes in he worksheet. |
|
| Back to top |
|
|
SharkD
General User
Joined: 17 Sep 2008
Posts: 19
|
| Posted: Thu Aug 06, 2009 10:56 am Post subject: |
|
|
| keme wrote: | =INDIRECT("Sheet1.$B"&$X4&":$B"&$Y4) defines a range from the row number stored in X4 to the row number stored in Y4. You can probably achieve exactly the same using the OFFSET() function::
=OFFSET(sheet1.B1;X4-1;0;Y4-X4+1;1) might do the job. (Depending on what behaviour you need when inserting/deleting rows/columns, you may want a different starting point, and different displacement values. Hence, jkrideau's question on what you want to achieve is still valid.)
The INDIRECT() function uses string representations of cell references, which makes the addressing "hardcoded". OFFSET() uses a real reference as the starting point, so it will update to reflect changes in he worksheet. |
Thanks, that looks like a winner! |
|
| Back to top |
|
|
|
