In Calc using Define Names to define a range of cells how do I define multiple non continuous cells? ie: A1:A4 AND C1:C4
You have to enter the ranges manually into the “Range” field. Use the ~ (Tilde) operator to connect the ranges, for example enter $Sheet1.$A$8:$B$9~$Sheet1.$D$8:$E$9. See topic “Reference operators” in help. Not all functions support ~ operator in their parameters, but for SUM for example it works.
Thanks for pointing this out. I was aware of the union operator (tilde) but did not realise it would work when defining named ranges.
Doesn’t seem to work with INDIRECT, though. Any alternatives?
Please refer to the answer by @Regina. Generally speaking, there are only two types of valid ranges in Calc:
- Arithmetical e.g., A1+B2-C3
- Reference e.g., A1:C3
These can be modified using the intersection ! and union ~ reference operators e.g., A1~C3. In general though, reference ranges are, by definition, contiguous. Bug fdo#35296 (RESOLVED as NOTABUG as it was treated as a documentation issue) is the related enhancement request to get non-contiguous reference ranges added to Calc. Bug fdo#53300 is the equivalent for non-contiguous ranges in charts.