| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
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 |
|
| Back to top |
|
|
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? |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
|
