calc - compute range based on boolean values

[DiesNuts]

Let’s say I have a range

 OFFSET($AA:$AA,start_,0,stop_-start_,1)~OFFSET($BD:$BD,start_,0,stop_-start_,1)~OFFSET($CG:$CG,start_,0,stop_-start_,1)

And let’s say I have three boolean fields named valid1, valid2, valid3.

How do I change the range formula s.t. OFFSET($AA:$AA,start_,0,stop_-start_,1) only gets in cluded when valid1 is true, OFFSET($BD:$BD,start_,0,stop_-start_,1) only when valid2 is true and OFFSET($CG:$CG,start_,0,stop_-start_,1) only when valid3 is true?

I.e. when, for example, (valid1,valid2,valid3)==(1,0,1), the range should be reduced to OFFSET($AA:$AA,start_,0,stop_-start_,1)~OFFSET($CG:$CG,start_,0,stop_-start_,1)

[Lupp]

Since there isn’t a thing like an ‘Empty Range’ I’m afraid there will not be a dircet way, and OFFSET might not be the appropriate means to get a workaround.
Constructs like =INDIRECT(IF(valid1;"A1:B15~";"")&"D3:Z77") are allowed. You may thus replace a reference concatenation by a text concatenation where an empty (not applicable) range is omitted by simply passing the empty text to the concatenation. I would try to avoid this.

[erAck]

INDIRECT evaluates only ranges, not expressions. In case valid1 is not 0 then the concatenation would produce the text A1:B15~D3:Z77 which INDIRECT does not digest due to the range union/list operator being used.

[Lupp]

Sorry! I had tested instead of first studying the specifications, and my testing was not thorough enough. Basically @erAck is right. There is, however, an additional marginal remark:
The colon is specified for two different purposes by OpenFormula: It is used as a syntactical element of range addresses (5.8), and may also be an operator usable with references (6.4.11). That INDIRECT does not return an error if offered an expression but just ignores operators and operands is a bug, imo.

[erAck]

Yes it is.

[erAck]

Submitted bug as tdf#104906.