We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Using Define Names how do I define multiple non continuous cells. [closed]

asked 2014-01-18 07:37:37 +0200

gasp gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-18 17:15:45.530134

2 Answers

Sort by » oldest newest most voted

answered 2014-01-18 14:22:18 +0200

Regina gravatar image

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.

edit flag offensive delete link more


Thanks for pointing this out. I was aware of the union operator (tilde) but did not realise it would work when defining named ranges.

oweng gravatar imageoweng ( 2014-01-18 21:36:45 +0200 )edit

Doesn't seem to work with INDIRECT, though. Any alternatives?

wleoncio gravatar imagewleoncio ( 2015-03-30 15:44:53 +0200 )edit

answered 2014-01-18 08:54:54 +0200

oweng gravatar image

updated 2014-01-18 21:44:08 +0200

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-01-18 07:37:37 +0200

Seen: 1,881 times

Last updated: Jan 18 '14