KnowledgeIsKey
New Member
- Joined
- Feb 2, 2016
- Messages
- 10
I have a workbook with 5 worksheets - the first one is a "master list" where I enter all tasks for a project.
It has the following columns:
A: Item
B: Due Date
C: Days Til Due
D: Status
E: Assigned To
F: Comments
In column C, it calculates the "days til due" based on the date and the "status" so if the status is "complete", it returns Complete otherwise, it calculates the date (unless it's blank in which it returns nothing). I got the formula for this through another posting and it is as follows (sorry for spaces, trying to make sure it shows up!)
= if (isblank(b2), "" , if(d2 = "Complete" , d2, b2 - today () ))
Conditional formatting works just fine on this particular page. I have the following formats in place (extra spaces included to avoid issues):
=INDIRECT("c"&ROW())="Complete" - which makes the row greyed out
=AND($C2< >"",$C2 <1) - which makes the row red
=AND($C2 >1, $C2 < 16) - which makes the row yellow
=AND($C2< >"",$C2 >15) - which makes the row green
On this sheet, it colors them according to the value in column c...
I've used this website: Link Excel Data across Multiple Sheets | MicroKnowledge, Inc. and followed the process listed to move the data from the master page to pages that have only certain info.
For example, the ones that would be "greyed out" are on a tab called "Completed" and those that are < 1 to due (meaning they're past due), are on a tab called "Overdue".
The issue is the yellow and green ones... at first, I tried to follow that process and get 2 separate tabs - one for "due within 15 days" and one "due after 15 days" but no matter what I tried with the queries, it kept returning dates BELOW 15 in the "greater than 15" section and NOTHING would show in the "between 1 and 16" section).
So, I've accepted that and made one tab called "upcoming" and just wanted to do conditional formatting to provide that same color coding to highlight those that are under 15 days and those that are above that... the issue is that the conditional formatting doesn't seem to be working. I'm using the same formulas as above but there is a cell that is 7 days until due date and yet it keeps highlighting in green...
Any thoughts? Or is there another way I can get this table to pull the data to separate tabs and have it be something that continually refreshes when new data is entered or updated so that the tracker is workable for this project?
Thanks for any help!!!
It has the following columns:
A: Item
B: Due Date
C: Days Til Due
D: Status
E: Assigned To
F: Comments
In column C, it calculates the "days til due" based on the date and the "status" so if the status is "complete", it returns Complete otherwise, it calculates the date (unless it's blank in which it returns nothing). I got the formula for this through another posting and it is as follows (sorry for spaces, trying to make sure it shows up!)
= if (isblank(b2), "" , if(d2 = "Complete" , d2, b2 - today () ))
Conditional formatting works just fine on this particular page. I have the following formats in place (extra spaces included to avoid issues):
=INDIRECT("c"&ROW())="Complete" - which makes the row greyed out
=AND($C2< >"",$C2 <1) - which makes the row red
=AND($C2 >1, $C2 < 16) - which makes the row yellow
=AND($C2< >"",$C2 >15) - which makes the row green
On this sheet, it colors them according to the value in column c...
I've used this website: Link Excel Data across Multiple Sheets | MicroKnowledge, Inc. and followed the process listed to move the data from the master page to pages that have only certain info.
For example, the ones that would be "greyed out" are on a tab called "Completed" and those that are < 1 to due (meaning they're past due), are on a tab called "Overdue".
The issue is the yellow and green ones... at first, I tried to follow that process and get 2 separate tabs - one for "due within 15 days" and one "due after 15 days" but no matter what I tried with the queries, it kept returning dates BELOW 15 in the "greater than 15" section and NOTHING would show in the "between 1 and 16" section).
So, I've accepted that and made one tab called "upcoming" and just wanted to do conditional formatting to provide that same color coding to highlight those that are under 15 days and those that are above that... the issue is that the conditional formatting doesn't seem to be working. I'm using the same formulas as above but there is a cell that is 7 days until due date and yet it keeps highlighting in green...
Any thoughts? Or is there another way I can get this table to pull the data to separate tabs and have it be something that continually refreshes when new data is entered or updated so that the tracker is workable for this project?
Thanks for any help!!!
