Den 2011-02-08 18:30:54 skrev Andreas Säger <[email protected]>:
Am 08.02.2011 18:02, Johnny Rosenberg wrote:
What I want is a cell function like ADDRESS(), but instead of a
reference to a single cell, I want a reference to a cell range.
ADDRESS(1;1;1) ⇨ $A$1
ADDRESS(2;2;4) ⇨ B2
I would like something like this:
RANGEADDRESS(1;1;1;2;2;4) ⇨ $A$1:B2
Can't find such a function, though. What do I miss?
Can I use ADDRESS() in some combination to obtain this anyway?
Here is a quick example of what I'd like to do:
=SUM(RANGEADDRESS(A1;B1;C1;D1;E1;F1))
OFFSET and INDEX return cell ranges from a start range and positional
numbers.
You calculate a text from positional numbers. That text is the same as
quoted ="$A$1" in a formula. It is just a sequence of letters, digits
and dollars. The reference is only in your head. INDIRECT
tries to convert such strings into references.
The following expressions return a reference to A1:
OFFSET(A1;0;0;1;1)
INDEX(A1:X999;1;1)
INDIRECT(ADDRESS(1;1))
OFFSET is the most versatile and easy to use (0 rows off, 0 columns off,
resized to 1 row and 1 column).
OFFSET(C4:D5 ; -1 ; -2 ; 3 ; 4) returns A3:D5 (1 up, 2 to the left, 3
rows, 4 columns)
I just read the help about this and it seems to be what I’m looking for.
Now there's only one minor problem: It doesn't work…
#VALUE!
I have OpenOffice.org 3.2.0 Swedish (the OFFSET function is called
FÖRSKJUTA in Swedish).
It works with addresses like ”A1”, that is single cells, then it returns
the value of the target cell.
--
Kind regards
Johnny Rosenberg
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
