OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Substitute for INDIRECT/LOOKUP?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
SharkD
General User
General User


Joined: 17 Sep 2008
Posts: 19

PostPosted: Wed Aug 05, 2009 8:33 am    Post subject: Substitute for INDIRECT/LOOKUP? Reply with quote

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
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Wed Aug 05, 2009 10:16 am    Post subject: Re: Substitute for INDIRECT/LOOKUP? Reply with quote

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
View user's profile Send private message
keme
Moderator
Moderator


Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

PostPosted: Wed Aug 05, 2009 3:18 pm    Post subject: Reply with quote

=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
View user's profile Send private message
SharkD
General User
General User


Joined: 17 Sep 2008
Posts: 19

PostPosted: Thu Aug 06, 2009 10:56 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group