google-sheetsconditional-formatting

Conditional Formatting in Google Sheets with another table INDIRECT

I want to highlight names in A7:A in color on the target sheet if a checkbox is marked in the source table "Data" (TRUE):

Source table "Data": Column A:A = Checkbox Column B:B = Names

The formula must first check the names of the target sheet with the names of the sheet Data!B:B and then check whether the checkbox in Data!A:A is set to TRUE.

I use semicolons as a separator, but I can't get any further. I think INDIRECT must be used?

=AND(NOT(ISBLANK(A7)); COUNTIF(Data!B:B; A7) > 0; VLOOKUP(A7; Data!B:A; 2; FALSE))
Solution

  • Try this alternative solution

    =AND(INDIRECT("Data!B:B")=A7, INDIRECT("Data!A:A")=TRUE)

    This formula uses INDIRECT to reference the Data sheet columns and checks if the name in the target sheet matches the name in "Data!B:B" and whether the checkbox in "Data!A:A" is TRUE. The AND function ensures that both conditions must be true for the formatting to be applied. If either condition is false, the formatting will not be applied.

    References