Thread: Format cell based on date look up data

I have a file which has a list of accounts, which is updated daily with data. I'm trying to format a cell, based on data in relation to todays date - basically so by a quick glance I can see who has not submitted data for today.

Attached is an example file.

I want to format the account name in colA, to look along row2, find todays date, and see if there is any data in the coresponding cells

So for my example file - AccountA, it would look for today (6th Apr), and see if there is data in cells H3&H4. There is, so no change.
AccountB has no data in todays cells (cells H6&H7), so the Account name in cell A needs to be highlighted.

I can get the formula working for looking at a specific cell, but don't know how to make do like a 'vlookup' along row2, to find the date before looking if there's any data in that column.


Hope that makes sense

Thanks in advance.

Hi,

You could use this formula

=COUNT(INDEX($C$3:$N$3,MATCH(TODAY(),$C$2:$N$2,0)),INDEX($C$4:$N$4,MATCH(TODAY(),$C$2:$N$2,0)))<2

to activate the conditional formatting.

Yup,

=COUNT(INDEX($C$3:$N$3,MATCH(TODAY(),$C$2:$N$2,0)),INDEX($C$4:$N$4,MATCH(TODAY(),$C$2:$N$2,0)))=0

will be true when there's nothing in either cell