Search code examples
google-sheetsconditional-formatting

google sheets conditional formatting across sheets [range trouble]

I need to format a range of cells in Sheet1 according to values in Sheet2. Explicitly, Cell Sheet1!B2 must be formatting according to value in Sheet2!B2, Sheet1!C10 according to Sheet2!C10 and so on...

This Conditional formatting formula works just fine:

  • RANGE: Sheet1!B2:B
  • FORMULA: =INDIRECT("Sheet2!B2:B")>100 (works fine, cell-by-cell)

But when I expand the range to something like the below, it formats nothing anywhere... It doesn't work:

  • RANGE: Sheet1!B2:C
  • FORMULA: =INDIRECT("Sheet2!B2:C")>100 (does nothing in either column B or C)

When I try the below it formats "by line", Sheet1!B2:C2, Sheet1!B10:C10, etc for instance:

  • RANGE: Sheet1!B2:C
  • FORMULA: =INDIRECT("Sheet2!B2:B")>100

I need formatting "by cell", for example Sheet1!B2, Sheet1!C10, and so on.

Solution

  • Slightly temperamental but please try clearing formatting from and selecting ColumnsA:F (or to suit, but whole columns for simplicity) and Format, Conditional formatting..., Format cells if... Custom formula is and:

    =INDIRECT("Sheet2!"&address(row(),column()))>100

    Then select formatting of choice and Done.