Hello everyone,
The last time I posted in here out of frustration...the assistance was LIGHTENING fast! And I need to present a working version today! lol. So here I am again. I spent the better part of yesterday trying to figure this out and then closed without saving the progress I made! I am using Excel 2002/XP (we will be upgrading to 2010 in the fall so when I close a spreadsheet...it wont close all open files!)
Here are the things I am trying to accomplish with the attached file:
1. Have the 'Due' column tabulate how many days there are between the 'BF' column and TODAY()
2a. If there are 6-10 days left - turn the cell yellow (text stays black)
b. If there are 2-5 days left - turn the cell red (text stays black)
c. If there are 1 or less days - turn the entire row red (text stays black)
3. sort by the red rows , then red cells , then yellow cells , non-colored cells
I hope that this is all possible with the version of Excel I am using...
The attached has your CF rules added. To sort, simply sort on the Due column values (you can't sort on colour).
Good luck.
AMAZING!! Thank you so much!!
One other quick question... As this is going to be a template for others, Is there any way to automatically sort the Due column?
You would need code to do that. Personally I would not recommend it as I think it is hugely offputting to have the data suddenly move as you are entering it.
Good luck.
ok... fair enough. I did forget one other conditional format question... (will be the last one...) I forgot to add the 'Completed' column and have attached a revised file. I would like to use an x in this column to represent when the task has been completed. Once there is an x entered all the formatting for that row would go back to normal and the cell in the 'Due' column would change to blank (or whatever would make it sort at the bottom). Is this possible? I am trying to make this template as user friendly as possible to try and avoid possible mistakes in deadlines...
There is no Completed column there, but you merely alter the Due formula to something like
=IF(OR(completed_column="X",E3=""),"",E3-TODAY())
Good luck.
Sorry, I attached the wrong file... your formula is perfect! Thank you again for the speedy assistance and have a great day!
My pleasure - you too!
Also, please do not forget to mark the question Solved in accordance with forum rules.
Good luck.
hi RLSaSSy - Actually i m new to this address..just now whle searching saw ur question on Cond formattting as i require similar kind of help on tis.hope they will respond soon, if replied can u pls inform me..thanks..naaz..
Originally Posted by RLSaSSy
Hello naaz. I have attached the final product with the conditional formatting for you. Hope this helps you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks