Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have a cell change to red/green if not equal to:
Hi and Thanks for looking.
We are working on a job tracking worksheet and one of our guys has done spectacular so far. I'll state the simple, then ask the Question there are 11 process's we track, left to right. 11 Phases and the 12th cell is ship date with a total varience running. Cells: B5 = Begin Phase 1 date B6 = Phase one complete date B7 = is variance between dates B5 and B6, or date sarted and date complete. I can allow 7 or less days for this phase. Question: If actual date in B6 exceeds (7)days" and shows, lets say "8 days" in B7, could we make B5 cell turn "red".???? If it is 7 days or less or equal to 7 turn green ? (In B5) Now assuming that is a possible scenario: Carry that a bit further.(please) 1. If B5 is red because because B7 is 7days. We still have time to catch up in Phases 2-11 (each with a no greater than 7 day time limit. Example through 7 phases I have 49 days. But if I get to Phase 7 in under 49 or better, Can we turn the cells to green to show we are back on schedule. I would sorta want the same for each cell. If Phase 1 is 8 days and Phase 2 comes back in 5 I'm one day ahead and I would like C5 to turn green, and red and green along those principals. Is this possible? Did I make any sense.? Of course I would always want N5 to be red or green That is the ship date.. Job Tracking by phase. I have the spread sheet done perfect to calculate the variance between cells 5 and 6. I just would like a visual color tracker between phases.. Any assistance would be great. AND if it can't be done just say so Thanks very much. Thank you, Mark Basically it's dates straight across. left to right with actual finish dates increasing as they go left to right as each phase takes time. When I enter a completion date in each cell I want a color update red or green. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have a cell change to red/green if not equal to:
Hi,
You can solve the first part of the question by using conditional formatting While in cell B5, go to format conditional formatting and under formula is type $B77 and select the font as red. Now click on Add and in formula is, type $B7<=7 and select the font as green. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mark" wrote in message ... Hi and Thanks for looking. We are working on a job tracking worksheet and one of our guys has done spectacular so far. I'll state the simple, then ask the Question there are 11 process's we track, left to right. 11 Phases and the 12th cell is ship date with a total varience running. Cells: B5 = Begin Phase 1 date B6 = Phase one complete date B7 = is variance between dates B5 and B6, or date sarted and date complete. I can allow 7 or less days for this phase. Question: If actual date in B6 exceeds (7)days" and shows, lets say "8 days" in B7, could we make B5 cell turn "red".???? If it is 7 days or less or equal to 7 turn green ? (In B5) Now assuming that is a possible scenario: Carry that a bit further.(please) 1. If B5 is red because because B7 is 7days. We still have time to catch up in Phases 2-11 (each with a no greater than 7 day time limit. Example through 7 phases I have 49 days. But if I get to Phase 7 in under 49 or better, Can we turn the cells to green to show we are back on schedule. I would sorta want the same for each cell. If Phase 1 is 8 days and Phase 2 comes back in 5 I'm one day ahead and I would like C5 to turn green, and red and green along those principals. Is this possible? Did I make any sense.? Of course I would always want N5 to be red or green That is the ship date.. Job Tracking by phase. I have the spread sheet done perfect to calculate the variance between cells 5 and 6. I just would like a visual color tracker between phases.. Any assistance would be great. AND if it can't be done just say so Thanks very much. Thank you, Mark Basically it's dates straight across. left to right with actual finish dates increasing as they go left to right as each phase takes time. When I enter a completion date in each cell I want a color update red or green. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have a cell change to red/green if not equal to:
Mark,
Select B5:M7 (with B5 as the actice cell), format all the cells for green, and then select Format / Conditional formatting... Formula is... with the formula =B$6($B$5+7*(COLUMN(B5)-1)) and then set your fill for red, and click OK. HTH, Bernie MS Excel MVP "Mark" wrote in message ... Hi and Thanks for looking. We are working on a job tracking worksheet and one of our guys has done spectacular so far. I'll state the simple, then ask the Question there are 11 process's we track, left to right. 11 Phases and the 12th cell is ship date with a total varience running. Cells: B5 = Begin Phase 1 date B6 = Phase one complete date B7 = is variance between dates B5 and B6, or date sarted and date complete. I can allow 7 or less days for this phase. Question: If actual date in B6 exceeds (7)days" and shows, lets say "8 days" in B7, could we make B5 cell turn "red".???? If it is 7 days or less or equal to 7 turn green ? (In B5) Now assuming that is a possible scenario: Carry that a bit further.(please) 1. If B5 is red because because B7 is 7days. We still have time to catch up in Phases 2-11 (each with a no greater than 7 day time limit. Example through 7 phases I have 49 days. But if I get to Phase 7 in under 49 or better, Can we turn the cells to green to show we are back on schedule. I would sorta want the same for each cell. If Phase 1 is 8 days and Phase 2 comes back in 5 I'm one day ahead and I would like C5 to turn green, and red and green along those principals. Is this possible? Did I make any sense.? Of course I would always want N5 to be red or green That is the ship date.. Job Tracking by phase. I have the spread sheet done perfect to calculate the variance between cells 5 and 6. I just would like a visual color tracker between phases.. Any assistance would be great. AND if it can't be done just say so Thanks very much. Thank you, Mark Basically it's dates straight across. left to right with actual finish dates increasing as they go left to right as each phase takes time. When I enter a completion date in each cell I want a color update red or green. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have a cell change to red/green if not equal to:
Bernie..WOW.. Thanks, I sorta understood Ashish's idea or formula which was
beyond me, however could you explain, why Select B5:M7 ( where my N7 is the aggregate +/- ship date(c7 thru M7) Should it read B5:N7 did I just write it wrong. AND could you explain: =B$6($B$5+7*(COLUMN(B5)-1)) Mostly the (COLUMN(B5)-1)) what does that do for this sheet. Thank you very much Mark Thank you. "Mark" wrote: Hi and Thanks for looking. We are working on a job tracking worksheet and one of our guys has done spectacular so far. I'll state the simple, then ask the Question there are 11 process's we track, left to right. 11 Phases and the 12th cell is ship date with a total varience running. Cells: B5 = Begin Phase 1 date B6 = Phase one complete date B7 = is variance between dates B5 and B6, or date sarted and date complete. I can allow 7 or less days for this phase. Question: If actual date in B6 exceeds (7)days" and shows, lets say "8 days" in B7, could we make B5 cell turn "red".???? If it is 7 days or less or equal to 7 turn green ? (In B5) Now assuming that is a possible scenario: Carry that a bit further.(please) 1. If B5 is red because because B7 is 7days. We still have time to catch up in Phases 2-11 (each with a no greater than 7 day time limit. Example through 7 phases I have 49 days. But if I get to Phase 7 in under 49 or better, Can we turn the cells to green to show we are back on schedule. I would sorta want the same for each cell. If Phase 1 is 8 days and Phase 2 comes back in 5 I'm one day ahead and I would like C5 to turn green, and red and green along those principals. Is this possible? Did I make any sense.? Of course I would always want N5 to be red or green That is the ship date.. Job Tracking by phase. I have the spread sheet done perfect to calculate the variance between cells 5 and 6. I just would like a visual color tracker between phases.. Any assistance would be great. AND if it can't be done just say so Thanks very much. Thank you, Mark Basically it's dates straight across. left to right with actual finish dates increasing as they go left to right as each phase takes time. When I enter a completion date in each cell I want a color update red or green. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have a cell change to red/green if not equal to:
Mark,
Yes, use through N7 if column N follows the same pattern, but you may need to change the formula logic a bit if column N is an "Aggregate"... Not sure what that means in this context. Basically, this part =B$6($B$5+7*(COLUMN(B5)-1)) means IF the date in row 6 (note that the cell address in the formula is B$6, which means the formula in each cell is evaluated against the value in row 6 of the same column) is greater than 7 times the number weeks (days) greater than the date in cell B5 (note that the address is $B$5, so it is the same for every cell when it is evaluated), then it is late. Basically, my formula just adds 7 days for each column - this part: COLUMN(B5)-1 returns the number of weeks (COLUMN(B5)-1 = 2 -1 = 1, then for cells in C, COLUMN(C5)-1 returns 3-1 = 2 (weeks) ) - multiplied by 7 gives you the days, and since one day in Excel = 1, adding to the original date does the date math for you. Have I explained that adequately? HTH, Bernie MS Excel MVP "Mark" wrote in message ... Bernie..WOW.. Thanks, I sorta understood Ashish's idea or formula which was beyond me, however could you explain, why Select B5:M7 ( where my N7 is the aggregate +/- ship date(c7 thru M7) Should it read B5:N7 did I just write it wrong. AND could you explain: =B$6($B$5+7*(COLUMN(B5)-1)) Mostly the (COLUMN(B5)-1)) what does that do for this sheet. Thank you very much Mark Thank you. "Mark" wrote: Hi and Thanks for looking. We are working on a job tracking worksheet and one of our guys has done spectacular so far. I'll state the simple, then ask the Question there are 11 process's we track, left to right. 11 Phases and the 12th cell is ship date with a total varience running. Cells: B5 = Begin Phase 1 date B6 = Phase one complete date B7 = is variance between dates B5 and B6, or date sarted and date complete. I can allow 7 or less days for this phase. Question: If actual date in B6 exceeds (7)days" and shows, lets say "8 days" in B7, could we make B5 cell turn "red".???? If it is 7 days or less or equal to 7 turn green ? (In B5) Now assuming that is a possible scenario: Carry that a bit further.(please) 1. If B5 is red because because B7 is 7days. We still have time to catch up in Phases 2-11 (each with a no greater than 7 day time limit. Example through 7 phases I have 49 days. But if I get to Phase 7 in under 49 or better, Can we turn the cells to green to show we are back on schedule. I would sorta want the same for each cell. If Phase 1 is 8 days and Phase 2 comes back in 5 I'm one day ahead and I would like C5 to turn green, and red and green along those principals. Is this possible? Did I make any sense.? Of course I would always want N5 to be red or green That is the ship date.. Job Tracking by phase. I have the spread sheet done perfect to calculate the variance between cells 5 and 6. I just would like a visual color tracker between phases.. Any assistance would be great. AND if it can't be done just say so Thanks very much. Thank you, Mark Basically it's dates straight across. left to right with actual finish dates increasing as they go left to right as each phase takes time. When I enter a completion date in each cell I want a color update red or green. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have a cell change to red/green if not equal to:
yes, vey much. You are so past me, I'm running to catch up. We will probably
change each of the phases to different day amounts so I may have to use the first formula. some actually will be </ 7 When I wrote aggregate, I should have not tried to be so smart and said N7 shows the total +/- days based on how many days we put in each phase., between C7:M7 If I can Just get C7 to turn font red or green based on </7 That would be a huge plus. Then I can work from there. Thanks is not enuff here. Mark "Mark" wrote: Hi and Thanks for looking. We are working on a job tracking worksheet and one of our guys has done spectacular so far. I'll state the simple, then ask the Question there are 11 process's we track, left to right. 11 Phases and the 12th cell is ship date with a total varience running. Cells: B5 = Begin Phase 1 date B6 = Phase one complete date B7 = is variance between dates B5 and B6, or date sarted and date complete. I can allow 7 or less days for this phase. Question: If actual date in B6 exceeds (7)days" and shows, lets say "8 days" in B7, could we make B5 cell turn "red".???? If it is 7 days or less or equal to 7 turn green ? (In B5) Now assuming that is a possible scenario: Carry that a bit further.(please) 1. If B5 is red because because B7 is 7days. We still have time to catch up in Phases 2-11 (each with a no greater than 7 day time limit. Example through 7 phases I have 49 days. But if I get to Phase 7 in under 49 or better, Can we turn the cells to green to show we are back on schedule. I would sorta want the same for each cell. If Phase 1 is 8 days and Phase 2 comes back in 5 I'm one day ahead and I would like C5 to turn green, and red and green along those principals. Is this possible? Did I make any sense.? Of course I would always want N5 to be red or green That is the ship date.. Job Tracking by phase. I have the spread sheet done perfect to calculate the variance between cells 5 and 6. I just would like a visual color tracker between phases.. Any assistance would be great. AND if it can't be done just say so Thanks very much. Thank you, Mark Basically it's dates straight across. left to right with actual finish dates increasing as they go left to right as each phase takes time. When I enter a completion date in each cell I want a color update red or green. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have a cell change to red/green if not equal to:
You are very appreciated:
If this works for us, this would be great. I notice that a test date in c5 of 01/01/2009 (Start) to C6 of 01/08/2009 (end) shows -7 is there a way to make this just 7 will the -7 force a problem in the font color change? -8 or 8 red -7 0r 7 green does the minus matter? Thank you very much. If phase 1 is 7 and turns red is <7 and is green "Ashish Mathur" wrote: Hi, You can solve the first part of the question by using conditional formatting While in cell B5, go to format conditional formatting and under formula is type $B77 and select the font as red. Now click on Add and in formula is, type $B7<=7 and select the font as green. Hope this helps. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Mark" wrote in message ... Hi and Thanks for looking. We are working on a job tracking worksheet and one of our guys has done spectacular so far. I'll state the simple, then ask the Question there are 11 process's we track, left to right. 11 Phases and the 12th cell is ship date with a total varience running. Cells: B5 = Begin Phase 1 date B6 = Phase one complete date B7 = is variance between dates B5 and B6, or date sarted and date complete. I can allow 7 or less days for this phase. Question: If actual date in B6 exceeds (7)days" and shows, lets say "8 days" in B7, could we make B5 cell turn "red".???? If it is 7 days or less or equal to 7 turn green ? (In B5) Now assuming that is a possible scenario: Carry that a bit further.(please) 1. If B5 is red because because B7 is 7days. We still have time to catch up in Phases 2-11 (each with a no greater than 7 day time limit. Example through 7 phases I have 49 days. But if I get to Phase 7 in under 49 or better, Can we turn the cells to green to show we are back on schedule. I would sorta want the same for each cell. If Phase 1 is 8 days and Phase 2 comes back in 5 I'm one day ahead and I would like C5 to turn green, and red and green along those principals. Is this possible? Did I make any sense.? Of course I would always want N5 to be red or green That is the ship date.. Job Tracking by phase. I have the spread sheet done perfect to calculate the variance between cells 5 and 6. I just would like a visual color tracker between phases.. Any assistance would be great. AND if it can't be done just say so Thanks very much. Thank you, Mark Basically it's dates straight across. left to right with actual finish dates increasing as they go left to right as each phase takes time. When I enter a completion date in each cell I want a color update red or green. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can I have a cell change to red/green if not equal to:
If each phase can have a different number of days, enter the allowed days in another row, say row 4,
and change the CF formula to =B$6($B$5+SUM($B$4:$B4)) HTH, Bernie MS Excel MVP "Mark" wrote in message ... yes, vey much. You are so past me, I'm running to catch up. We will probably change each of the phases to different day amounts so I may have to use the first formula. some actually will be </ 7 When I wrote aggregate, I should have not tried to be so smart and said N7 shows the total +/- days based on how many days we put in each phase., between C7:M7 If I can Just get C7 to turn font red or green based on </7 That would be a huge plus. Then I can work from there. Thanks is not enuff here. Mark "Mark" wrote: Hi and Thanks for looking. We are working on a job tracking worksheet and one of our guys has done spectacular so far. I'll state the simple, then ask the Question there are 11 process's we track, left to right. 11 Phases and the 12th cell is ship date with a total varience running. Cells: B5 = Begin Phase 1 date B6 = Phase one complete date B7 = is variance between dates B5 and B6, or date sarted and date complete. I can allow 7 or less days for this phase. Question: If actual date in B6 exceeds (7)days" and shows, lets say "8 days" in B7, could we make B5 cell turn "red".???? If it is 7 days or less or equal to 7 turn green ? (In B5) Now assuming that is a possible scenario: Carry that a bit further.(please) 1. If B5 is red because because B7 is 7days. We still have time to catch up in Phases 2-11 (each with a no greater than 7 day time limit. Example through 7 phases I have 49 days. But if I get to Phase 7 in under 49 or better, Can we turn the cells to green to show we are back on schedule. I would sorta want the same for each cell. If Phase 1 is 8 days and Phase 2 comes back in 5 I'm one day ahead and I would like C5 to turn green, and red and green along those principals. Is this possible? Did I make any sense.? Of course I would always want N5 to be red or green That is the ship date.. Job Tracking by phase. I have the spread sheet done perfect to calculate the variance between cells 5 and 6. I just would like a visual color tracker between phases.. Any assistance would be great. AND if it can't be done just say so Thanks very much. Thank you, Mark Basically it's dates straight across. left to right with actual finish dates increasing as they go left to right as each phase takes time. When I enter a completion date in each cell I want a color update red or green. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change the color in a chart, so POSITIVE values is e.g. GREEN and | Charts and Charting in Excel | |||
change cell color to red & then green when value change in 2 cel | Excel Discussion (Misc queries) | |||
Change a cell color based on a certain value (green or red) | Excel Worksheet Functions | |||
get a cell to change colour from a value, eg 1to7=green etc | Excel Discussion (Misc queries) | |||
can macros change cell color daily, i.e. green to red everyday? | Excel Worksheet Functions |