• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Help With CF Rules [SOLVED]

S

shane_kidani

New Member
I have a spreadsheet that I'm working on. What I would like to do is have the cells in the range fill/highlight with a certain color, based on the current date and the date a training activity is due.


For example, I have a chart of 17 employees with 10 training activities that i'd like to monitor. For the sake of clarity, let's say the due dates for these trainings are all the same (6/30/13). So, my chart would have 19 rows (headers plus+due dates+employees) and my chart would have 11 columns (employee names + training activity names).


I want to accomplish something quite similar to Chandoo's tutorial on highlighting due dates: http://chandoo.org/wp/2012/05/22/highlight-due-dates-excel/

My goal is to have the cells within the specified range turn green on the following conditions:

1. No date is entered into the cell;

2. Due date for activity is 30 or more days from expiring.


Chandoo's code in his example is: =AND(MEDIAN(TODAY()+1,$C6,TODAY()+7)=$C6,$D6="")

Where "$D6" is his condition to look up. So, if Excel looks at D6 or below, and finds "Yes" in a cell, then it changes text to a dull gray, otherwise, it highlights the cell range either red or orange, based on the due date.


Ultimately, I'd like to make the cells in range turn green if 30 or more days from expiration; yellow if between 10 and 29 days from expiring; orange if within 5-9; and red if 4 or less or past expiration date.


The problems I've encountered are:

1. Only the top row in range changes with the due date changing;

2. All cells in range will change, giving the impression of working properly, but then only top row changes when due date changes


I've done some reading, and I realize that there may be a conflict in the logic of the rules, so if somebody could help me understand where I'm going wrong, that would be great.
 
Hi, shane_kidani!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about questions in general...


If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.


And about this question in particular...


I assume that you've already downloaded the sample file of the posted link, but just in case:

http://img.chandoo.org/cf/highlight-if-due.xlsx


Without knowing your data I can only think on saying that you'd check the proper order for the CF rules and if any has set the Stop On True attribute. Note that in that example the first rule is that of red, then that of orange, and then that of Yes. So despite of any of the 2 first is applicable the 3rd one overrides them.


If this doesn't help then consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!
 
SirJB7,

Thank you for your quick response. I've read through the green and I'm quite familiar with forum etiquette; I help people with PC problems on another forum. I've also tried finding a topic close to mine, but alas, nothing even close, well at least not in the first three pages.


I can post a sample later, but for now, this test demonstration will have to do:

[A] [C]

[1]

[2]

[3]

6/30/13


In the above illustration, the range is A1:C3. I want A1 to turn green if a due date is 30 days any beyond. For the same range, i'd like the cell to turn yellow if deadline is 15-29 days, etc.


In the selected range, I used =TODAY()-$A4<=30

After inserting that formula, I can get the range to turn green; however, if I change the date in A4, only the top row (A1:C1) will revert back to white. If I delete the date, then all the cells in range revert back to white.


If you can make sense of this, great, otherwise, I'll try to post a sample, but that won't be for at least 6-7 hours.
 
Hi, shane_kidani!


But that differs from the original example as that had one cell per row to be tested (as of date value) and that's why the CF rule had fixed column but relative row ($A4), so each time the cell D4 (or it's CF) was copied down the rule changed to $A5, $A6, and so on.


In your example you have a unique cell for all rows to be tested so the CF rule should be $A$4 in order to fix column and row and when cell A1 get copied to A2 and A3 and so on, the condition vs $A$4 doesn't change, as now changes to $A5, $A6...


Hope it helps.


Regards!
 
Thank you again, SirJB7. Your explanation does indeed help. As you can tell, i'm but a novice at stuff. I'll submit a sample of what it is I'm working on in a few hours. I believe that my problem now is that rules I created conflict each other.
 
All right... Here is the link to the sample worksheet. Sheet 1 shows the problems that I ran into. I have descriptions of the problems next to the charts. I believe your explanation above address this problem.


Sheet 2 is the actual sample in which I would like to find a way to highlight blank cells within range, depending on the time left to deadline.


https://docs.google.com/file/d/0B8STya1ozOtrN3E1ekV3X1RNdDQ/edit?usp=sharing
 
Hi Shane ,


I am not able to understand your requirement ; can you please clarify ?


Is the due date going to be in the future or in the past ?


If the due date is going to be in the future , then how do you want your CF formula to work ? Should the cells be colorless as long as the due date is far away ? Should the colors start appearing only as the due date nears ? If so , then the formula should be Due_Date - Today() and not the other way around.


It can be that as long as the due date is more than 15 days away , the cells appear Light Green ; between 15 days and 5 days , they appear Light Yellow , between 5 days and 0 days they appear Amber , and once the due date is passed , they turn Red.


Narayan
 
Hi, shane_kidani!


In worksheet Sheet1 you have 3 CF rules:

=TODAY()-$B6<=15, yellow applied to $B$11:$E$15 (2nd table)

=TODAY()-$B16<=5, red applied to $B$11:$E$15 (2nd table)

=TODAY()-$B7<=15, re applied to $B$2:$E$6 (1st table)


For the 3rd table ($B$20:$B$24) you paint each of the 4 columns in green, yellow, orange and red, but you don't specify any rule or how should those cells be painted. And there are no rules at all for green and orange, so what're you trying to do?


Please elaborate.


Regards!
 
SirJB7,


Next to table 3 I put an example:


Example of highlight if due date is X days away

where "X" = date range of:

30+

15 to 29

6 to 14

< 0 to 5


Under each column is a hypothetical date.


So, given my goal, if due date is 7/30/13, it is 30+ days away, so I would like the blank cells to turn green. There is no CF rule, because you asked for a manual example of what I'm trying to accomplish.


If I have confused you, I apologize. The first sheet was meant to show the problems i've been encountering while trying to apply the highlight CF rule. That is, with the rule applied to a range of cells, only the top row in that range is effected.


I'll try to simplify my goal...


For the sake of understanding, let's use the chart/table on sheet 1, but we'll just talk about user 1 and lesson 1, and then I'll apply the rule to the entire needed range.


So...


If Lesson 1 has an due date of 7/30/13, because the due date is 30 or more days away, I'd like that cell to turn green if cell is blank. This whole excercise is going to be a way to identify how long before an employee is past due on a training event, w/o having to look at every cell.
 
NARAYANK991,


The dates for the actual outcome will all be in the future, but for purposes of testing the rule(s), I used a date that has passed. In other words, I wanted to know if I had the formula right, so if formula was right, blank cells would highlight red because the referrenced due date has passed.


The formulae should work in such a way that these conditions are met:


1. If cell is blank, compare to due date in that column (for sake of clarity, just assume B2 is all that needs CF rule, I'll work out the range afterwards)

2. If column is blank, fill dark green if due date is 30 or more days away

3. ...yellow if between 15 and 29 days away

4. ...orange if between 6-14

5. ...red if 5 or less or past due
 
Hi Shane ,


With the clarifications you have given , select your entire range C4:P17 on Sheet2 , and add the following rules , only two of which I have given ; you can fill in the remaining ones :


=AND(ISBLANK(C4),C$19-TODAY()>=30)


=AND(ISBLANK(C4),C$19-TODAY()>=15,C$19-TODAY()<=29)


Note that C4 is the first cell in the selected range ; you cannot use the $ sign in this , since it will be applied to cells whose row and column numbers will be different.


Note the use of the $ sign in C$19 , so that the row is fixed.


Note the use of C$19 - TODAY() , and not the other way around , since your due dates are going to be in the future.


Narayan
 
You must log in or register to reply here.
Back
Top