0

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.

Improve this question

1 Answer 1

Reset to default
0 
=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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks. I'm trying to highlight the corresponding cells in column A. It would be dynamic so the highlight only applies to each new day.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.