[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
bluemagician
Newbie

Joined: 23 Apr 2006
Posts: 2

 Posted: Mon Dec 03, 2007 10:52 am    Post subject: Derive Range Names? Hi, is it possible to derive range names from other data in my sheet? Let's say I got two ranges, names "range1" and "range2". Now I have a formula that uses one of these two ranges, but which one depends on some other data in the sheet. So is it possible to do something like =FORMULA(range+"data") instead of =FORMULA(IF(data=1;range1;IF(data=2;range2;doNothing)) ? CU FLo
h1h
OOo Enthusiast

Joined: 18 Jun 2006
Posts: 152
Location: Switzerland

 Posted: Mon Dec 03, 2007 3:38 pm    Post subject: there is the indirect function, which however does not work on named ranges. Workaround: use a helper cell which has text such as b11:b22, name that cell as range one and then use e.g. sum(indirect(range1))_________________OOo 2.3 on Linux
huwg
Super User

Joined: 14 Feb 2007
Posts: 890

 Posted: Tue Dec 04, 2007 2:29 am    Post subject: This is issue 4904 isn't it? It's now marked RESOLVED FIXED, but targeted for OOo 3.0 - what does that mean?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10065
Location: Germany

Posted: Tue Dec 04, 2007 1:31 pm    Post subject:

Sum Nth row in range:
 Code: =SUM(INDEX(range;N;0))

Sum Nth column in range:
 Code: =SUM(INDEX(range;0;N))

Sum Nth sub-range in range:
 Code: =SUM(INDEX(range;0;0;N)

where range is something like (A1:B2;D1:E2;...) e.g =SUM(INDEX((\$A\$1:\$B\$2;\$D\$1:\$E\$2);0;0;2)) sums D2:E2

SUM the Nth 5x2 rectangle in range
 Code: =SUM(OFFSET(range;5*N;0;5;2)

All this works by means of positions rather than names. You are free to use a helper table mapping names to positions:
range1 1
range2 6
range3 11
 Code: =SUM(OFFSET(range;VLOOKUP("range"&N;helper;2;0);0;5;2)

Then you may use a label in column A, match that label and get some cells from Bxxx to Cyyy
 Code: =SUM(OFFSET(\$A\$1;MATCH("range"&N;\$A\$1:\$A\$1000;0)-1;1;5;2)

INDIRECT converts a string to reference. If "A"&N yields "A99" then INDIRECT("A"&A1) returns the value in A99. In Calc this does not work with names so you can not use concatenated names like "range"&N
Again you can set up a helping table.
Menu:Insert>Names>Insert.... button [Insert All] prints a list of names like this:
 Code: range1  =\$Sheet1.\$A\$1:\$B\$2 range2  =\$Sheet2.\$A\$1:\$B\$2 range3  =\$Sheet3.\$A\$1:\$B\$2

Add a third column removing the leading "=" from 2nd column =MID(B1;2;LEN(B1))
Name the 3 columns name list and use something like this:
 Code: =SUM(INDIRECT(VLOOKUP("range"&N;helper;3;0))

The positional approach with INDEX and OFFSET is more flexible than using names, but many Excel files use something like INDIRECT("range"&A1) where "range"&A1 yields a defined named reference.
If you really need to use this construct in Calc, try to replace INDIRECT with BASINDIRECT where the latter is a userdefined Basic function http://www.oooforum.org/forum/viewtopic.phtml?t=61265
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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