Conditional Formatting a Lookup List

[Alex501]

Hi All

I have a sheet with a date in column A and a lookup list in column J, i'm trying to change the lookup list item to 'Exhausted' once 30 days have passed sonce the imput date (column A).

I can change the colour using conditional formatting with;

=A2<TODAY()-30

However I can't seem to get it to change to the lookuplist item.

Any suggestions?

Thanks

Alex

 

[etaf]

whats in the lookup list, how many entries and what range - not sure i follow
maybe an if()

=IF(=A2<TODAY()-30 , "exhausted", "whatever should be in before")
but you may need vba - not my area - if you are changing a lot of data

 

[Alex501]

whats in the lookup list, how many entries and what range - not sure i follow
maybe an if()

=IF(=A2<TODAY()-30 , "exhausted", "whatever should be in before")
but you may need vba - not my area - if you are changing a lot of data

Hi etaf, thanks for the reply, there's only four entries in the lookup list;
Closed
Exhausted
Do Not Contact
Converted

I thought as you and used

=IF(=A2<TODAY()-30 ,"Exhausted","")

However I'm assuming that would change anything less than 30 days to blank. Saying taht it doesn't change the ones older than 30 days to Exhausted.

Thanks

 

[etaf]

well you could put the formula in each entry
=IF(A2<TODAY()-30 ,"Exhausted","Closed")
etc