0

I have a list of time intervals along with their convenience ratings. I wish to highlight intervals rated anything other than impossible which conflict with those rated impossible. I use start1 and end1 named ranges to refer to the interval endpoints and convenience is self-explanatory.

I couldn't do it with ISBETWEEN, but the following formula appears to correctly return true for all such intervals and false for others:

=OR(ARRAYFORMULA(
  IF($C1<>"impossible", 
     IF(GTE($A1,FILTER(start1,convenience="impossible")),
        IF(LTE($A1,FILTER(finish1,convenience="impossible")),TRUE)
     )
  )))

However, when I enter this in Conditional Formatting it is rejected as invalid formula.

How can I add it to the conditional formatting rule?

Here's the spreadsheet. The data in question is the sheet named "TEST" (the last one). The formula has been entered in column P.

Improve this question
1
  • Hi and welcome. You say I wish to highlight intervals rated anything other than impossible which conflict with those rated impossible Would you please edit your spreadsheet (and include a snapshot in your question) to provide an example of how the conditional formatting would appear if you had a successful outcome. Perhaps it is just me, but I'm confused about the specific columns/rows to which you want to apply conditional formatting.
    – Tedinoz
    Sep 5, 2021 at 2:31

1 Answer 1

Reset to default
-1

It was very simple. I had just forgotten that in conditional formatting, references to named ranges must be made using the INDIRECT function.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.