1

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

=COUNTIF(X:X;A1)

It worked instantly.
What am I doing wrong?

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. – Mihail Malostanidis Mar 6 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 at 12:31
  • Alright, sounds like a bug :D – Mihail Malostanidis Mar 6 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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