The attached file has 2 columns that I want formatted based on detailed criteria I outlined in the file. I currently have 2 basic formulas, but these are confusing since I'm unable to create the correct formulas. Please see attached file... Thx
The attached file has 2 columns that I want formatted based on detailed criteria I outlined in the file. I currently have 2 basic formulas, but these are confusing since I'm unable to create the correct formulas. Please see attached file... Thx
Please try CF formula applies to N7:O126
Fill Green
=AND($M7="In Progress",N7< TODAY(),INDEX($CW7:$DA7,,COLUMNS($N7:N7)*4-3)<>"")
Fill Yellow
=AND($M7="In Progress",N7=TODAY(),INDEX($CW7:$DA7,,COLUMNS($N7:N7)*4-3)="")
Fill Red
=AND($M7="In Progress",N7< TODAY(),INDEX($CW7:$DA7,,COLUMNS($N7:N7)*4-3)="")
Hi -
The location of the columns changed recently and now the formulas provided before are not working. I've tried changing the formula a bit but is still not working. Any ideas? I've attached an updated file to show the new location of the columns. Thx
I did modify the formulas that Bo_Ry provided and worked back in Dec-2018 with the new columns. I also try to adjust the *4-3 but to no avail. Am I missing something? Thx
This has been solved. Another formula in the existing columns was causing the issue. It was resulting 1/0/00 which in turn caused the conditional formula not to work. Thx
I ran into a snag with the existing conditional formula I'm using in columns P and Q. The formula will highlight the date as red if the target date has not been met and is past due from columns FK:FL and FO:FP. However, columns P & Q now bring in dates from BN:BO and FO:FP for the Agile Workflow paths (column G). It highlights the date as past due when it has been met (see row 45). I've attached a sample file.
Current formula
=AND($O45="In Progress",P45< TODAY(),INDEX($FL45:$FP45,,COLUMNS($P45:P45)*4-3)="")
Check All below condition must be True to highlight.
1. $O45="In Progress"
2. P45< TODAY()
3. FL =""
Please walk me through step by step
Check column P with FK, FL, FO, FP, BN, BO for =, > or < or what?
Current formula
=AND($O45="In Progress",P45< TODAY(),INDEX($FL45:$FP45,,COLUMNS($P45:P45)*4-3)="")
Check All below condition must be True to highlight.
1. $O45="In Progress"
2. P45< TODAY()
3. FL =""
Please walk me through step by step
Check column P with FK, FL, FO, FP, BN, BO for =, > or < or what?
My Comment:
The current formula is looking at the FL and FP columns to see if the milestone date has been met. If the "Actual" date is populated, than met. If the "Actual" date is NOT populated and the "Target" date is already past due (TODAY), than NOT met. That's when the date in columns P or Q are highlighted red to quickly indicate at a glance w/o having to scroll right that the milestone is past due.
The issue is that the Agile Workflow milestones are not updated on FL and FP columns, these are on BM and BO... therefore, the current formula is not working for these...
Perhaps the following will help:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks