I need help setting up conditional formatting that will color names in column A starting from row 7 when a corresponding checkbox is checked on another worksheet.
My setup looks like this:
Main sheet has names in column A (A7 and down)
Reference sheet called “Data” has checkboxes in column A and matching names in column B
What I want to happen is the formula should match names between the two sheets and then check if the checkbox for that name is marked as TRUE in the Data sheet.
I tried building a formula but got stuck. I think I need to use INDIRECT but not sure how to make it work properly. Here’s what I have so far:
Your VLOOKUP won’t work because it needs the lookup column on the left, but you’ve got checkboxes in column A and names in column B. Here’s a better approach: =AND(NOT(ISBLANK(A7)); COUNTIF(Data!B:B; A7) > 0; INDIRECT("Data!A" & MATCH(A7; Data!B:B; 0))). MATCH finds which row your name is in column B, then INDIRECT builds a reference to the checkbox in that same row’s column A. You’re checking the actual checkbox value instead of wrestling with VLOOKUP backwards. Just make sure your checkboxes return TRUE/FALSE values.
Try this: =AND(A7<>""; ISNUMBER(MATCH(A7;Data!B:B;0)); INDEX(Data!A:A;MATCH(A7;Data!B:B;0))=TRUE). It finds the row where your name matches in Data column B, then checks if the checkbox in column A is true for that row. Way better than VLOOKUP here.
Your VLOOKUP has the right idea but the range is backwards. When you write VLOOKUP(A7; Data!B:A; 2; FALSE) you’re telling it to search column B first, but VLOOKUP always searches the leftmost column. Since checkboxes are in column A and names in B, you need Data!A:B as the range, then column index 1 for the checkbox value. Actually, scratch that - it still won’t work because you’re looking up the name but names are in column B, not A. INDEX/MATCH works better here: =AND(NOT(ISBLANK(A7)); INDEX(Data!A:A; MATCH(A7; Data!B:B; 0)) = TRUE). This finds the name’s position in column B and grabs the matching checkbox value from column A.