Conditional Formatting- Mixed Data
Hi,
I have a list of data with various dates and the occasional blank or N/A. Is it possible to have a formula alert me when a date is within two weeks of today's date (in the future) that turns off when another cell has a value of approved?
My issue is that when I use a conditional formatting formula like =(A2-14)<TODAY() the cell alerts dont go away.
Thanks for the help!
See attached data
you can do it with the formula like this
=AND(A2<>"approved",B2-14<TODAY())
pease see attached for the formating in use.
If you liked my solution, please click on the Star -- to add to my reputation
If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.
Hi needyohelp,
See the attached file and let me know if this works for you. Thanks
Regards,
DILIPandey
<click on below 'star' if this helps>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com
..Excuse me, but,@DILIPandey.
Has your suggestion something different than DGagnon's..??
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Yes... and just to list them :-
1) the dollar ($) sign in the CF logic
2) the choice of color (different red shade)
3) the range A17:A18 are different i.e., values are changed ( I believe that is done for testing purpose)
and even the attached file name.. are different
Also, just noticed that I have taken around 9 mins in checking the differences and drafting this post.. and it is quite possible that some one else has already replied highlighting these differences <by the time I am drafting this email>. please don't assume that I have taken the details (of differences) from that person's post
Regards,
DILIPandey
<click on below 'star' if this helps>
Oh... great....!!
After clicking on the button "Post Quick Reply", I got to know that no one else has replied in between... so I am safe.. right
Thanks.
Regards,
DILIPandey
<click on below 'star' if this helps>
Nice joke!!!...........
I also noted the same differences.
Originally Posted by dilipandey
Yes... and just to list them :-
1) the dollar ($) sign in the CF logic
2) the choice of color (different red shade)
3) the range A17:A18 are different i.e., values are changed ( I believe that is done for testing purpose)
and even the attached file name.. are different
Cheers
and heartiest thanks for your kind understanding... my Friend(s) ...
Also, I am expecting a auto system.. which can tell me -> "hey dilipandey, you have take a little long while solving this query, mr.xyz has already replied with similar solution hence go ahead and scrap your efforts and don't dare to post, else you'll be in question"
Regards,
DILIPandey
<click on below 'star' if this helps>
Best way in this case(I know this from my experience......) is to delete our answers.
At least what i do, anymore.....
Cheers...
ha ha ha..
I see it as "two great minds - think alike"
and just wanted to keep my solution as a proof of above statement, so that my wife can't question my capabilities....
Regards,
DILIPandey
<click on below 'star' if this helps>
If thats the case i would not use my posts as a standard for 'Great Minds'
ha ha ha... !!
that one is little tricky... i guess..
Regards,
DILIPandey
<click on below 'star' if this helps>
This is perfect thanks. However if there is a blank in the date coloumn the cell is red. Is there anyway to avoid this?
And I can't find a star to click on, but I do like your post.
=AND(A2<>"approved",B2-14<TODAY(),B4<>"")
and the star is to the bottom right just bellow the post, its a 6 pointed star. feel free to 'rep' any members who contributed positivly to your thread.
I'm getting a litte confused as the data I gave you isn't exactly what mine looks like. See the attached for a better idea of what I'm working with.
I input the formula you gave me and it doesnt seem to be working. To recap I'm looking to have the reapproval cell turn red when the reapproval date is within 2 weeks (in the future), and for the red alert to go away when there is an approval in the "I" coloumn. Also if the reapproval date is past the current date and has not been approved I would like it to stay red so I know it needs to be taken care of, and go away when approved.
I hope this isn't too confusing.
data.xlsxThanks!
try editing your conditional format formula to this:
=AND(I2<>"approved- no change",J2-14<TODAY(),J2<>"")
i think it is working based on the decription you provided.
note: the data you provided has no lines that should be highlighted based on this formula
I've input a test area that contains cells with relevant dates. The approved cell doesnt seem to have an effect. data.xlsx
you need to change the word "Approved" to "approved- no change" as that is what is actualy in your data
I actually have it as "approv*" in the date to account for variations and it still doesnt seem to be working.
NVM I got it, thanks for the help!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks