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)
Output: 0
INDEX(A1:X999;1;1)
Output: 0
INDIRECT(ADDRESS(1;1))
Output: 0
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)
Doesn't seem that any of these does what they are supposed to do. Could
anyone else test this?
--
Kind regards
Johnny Rosenberg
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
