I needed to check which indexes exist on another sheet.
So for the A:A range, I made conditional formatting with the following formula:

=COUNTIF('Other Sheet'!A:A;A1)

That had a red error outline.
Then I filled column X with ='Other Sheet'!A:A and used the formula


It worked instantly.
What am I doing wrong?

1 Answer 1


It can, but you need to wrap it in INDIRECT:

=COUNTIF(INDIRECT("'Other Sheet'!A:A"); A1)

  • Thank you. Can you clarify why it only works this way? Are there other contexts only INDIRECT will work? I thought it was dynamic (from string) but still worked in the current context. This breaks that assumption. Mar 6, 2019 at 12:27
  • 1
    this is only conditional formatting specific. not sure if its somewhere mentioned at google sheets help pages tho, but it works only if you use indirect to reference another sheet's range...
    – user0
    Mar 6, 2019 at 12:31
  • Alright, sounds like a bug :D Mar 6, 2019 at 12:40

Your Answer

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

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