#  Other Applications & Softwares  > Outlook Formatting & Functions >  >  Format cell based on date look up data

## dancing-shadow

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  :Smilie:  

Thanks in advance.

----------


## sweep

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.

----------


## dancing-shadow

> =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



Works perfectly!! 

Is there a way to make it look whether there data in either of the cells (eg. row 3 OR row 4), as sometimes I have one figure/data but not both, and I'm only interested if there is no data at all in either cell. 


Many thanks

----------


## sweep

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

----------


## dancing-shadow

> =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



Awesome-sauce :D 

Many super thanks for your help, you've saved me a lot of time  :Smilie:

----------


## dancing-shadow

Having found the above formula to work perfectly, I've also realised I need it to miss-out Saturday/Sundays. So that if today's date falls on a Monday, the formula needs to look at Friday instead. 

I'm sure there is an IF(WEEKDAY(TODAY().... bit that can go in to do this, but I'm simply not clever enough to write it!!

Many thanks again  :Smilie:

----------

