CODEWITHSUNDEEP
google-sheetsconditional-formatting
Jon N
Jon N

Reputation: 11

Conditional Formatting For a Checklist Using AND function with DATE and Checkboxes

I'm trying to make a list with dates and checkboxes. How can I use conditional formatting to highlight only cells in column A if another column, row 1 is today's date and the checkboxes underneath in the corresponding rows are true? Also, its ok if it highlights the entire row.

Using the custom formula in conditional formatting

=AND(INDIRECT(ADDRESS(1,COLUMN(),4))=TODAY(), B2=TRUE)

with the range A2:A6, I didn't get any results.

enter image description here

*EDIT: I've figured it out. It's =AND(OFFSET($A1, 0, MATCH(TODAY(), $1:$1, 0) - 1) = TRUE)

MATCH looks for the column number with today's date. OFFSET looks for the checkboxes that are true in that column. AND ensures both are true so that the formula only affects checkboxes for today's date.

Upvotes: 0

Views: 89

Answers (1)

user11222393
user11222393

Reputation: 5501

=filter($B2:$H2,$B$1:$H$1=today())

or

=xlookup(today(),$B$1:$H$1,$B2:$H2)

applied to range A2:A5

Result:

enter image description here

Upvotes: 0

Related Questions