For some reason I want to make a named range computable.
If the named range refers not to an absolute name (address), but to a calculated one (that is, formula expression is used), then the ReferredCells method does not want to return CellRange. How to calculate the address of the range?
Suppose that:
MyNamedRange = $Sheet1.$A$2:$B$3
Then add a column labels in the 1st row to it:

$Sheet1.$A$1:INDEX(MyNamedRange)
Or: OFFSET(MyNamedRange;1;0;ROWS(MyNamedRange)+1)
So, 1
and 2
are the formula expressions that we define in the Manage Names
dialog.
We define the second range, i.g. MyEntireRange
, using formula 1
or 2
.
oDataRange = ThisComponent.NamedRanges.getByName("MyEntireRange").ReferredCells
An error occures. The range must be calculated and its content must contain an absolute name.
But everything works well on the sheet when I use MyEntireRange
.
="Row/Column Count: "&ROWS(MyEntireRange)&"/"&COLUMNS(MyEntireRange)
How do I get a range in the code? How to calculate (evaluate) the address using the above formulas (1, 2) if oNamedRange.Content
is a string to evaluate, but not an absolute name?
And one last question. The OFFSET
formula is volatile, but the INDEX
is not. If the volatile function is used not on the sheet, but in a formula expression (see above), will it also be recalculated whenever any data on the sheet changes?
namedrangeformula.ods (11.0 KB)