Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting help
Hello,
I posted a question last night but see that I have one piece to finish my spreadsheet. Here it is... I have a Gantt Chart that I am developing in excel. Basically I have conditional formatting that compares the date range of a particular task to the actual date and thus fills in small excel squares showing visually how things are progressing in the timeline. It shows in little green squares When the task starts and when it is supposed to end. Here is a screenshot of what it looks like. http://download.shireinteractive.com/images So basically as you can see on row 31 I have the text grayed out because 'X" is in the field B31. I would like to apply that same grayed out effect to the green date range on the right in row31. Currently that date range code looks like this. =IF(AND(V$3=$E13,V$3<=$F13),1,"") There is then a conditional formatting applied to that cell telling it that if it equals "1" to make that cell green. Right now anything that is not in the date range is white, Anything that is in the date range is green. I want to add one more component, anything that is in the date range and has an "x" in the B column in that row, I want it to turn that green date range to Gray. Is this possible? If this doesn't make sense please let me know and I will try to explain in a different manner. I have also posted the excel spreadsheet in that link above for your convenience. As always thanks for your time. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting help
I have updated your file to show how to do it. It is a zip file, as it was
too big to upload it vanilla. Get it at :http://cjoint.com/?kupXBMVtGM -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hello, I posted a question last night but see that I have one piece to finish my spreadsheet. Here it is... I have a Gantt Chart that I am developing in excel. Basically I have conditional formatting that compares the date range of a particular task to the actual date and thus fills in small excel squares showing visually how things are progressing in the timeline. It shows in little green squares When the task starts and when it is supposed to end. Here is a screenshot of what it looks like. http://download.shireinteractive.com/images So basically as you can see on row 31 I have the text grayed out because 'X" is in the field B31. I would like to apply that same grayed out effect to the green date range on the right in row31. Currently that date range code looks like this. =IF(AND(V$3=$E13,V$3<=$F13),1,"") There is then a conditional formatting applied to that cell telling it that if it equals "1" to make that cell green. Right now anything that is not in the date range is white, Anything that is in the date range is green. I want to add one more component, anything that is in the date range and has an "x" in the B column in that row, I want it to turn that green date range to Gray. Is this possible? If this doesn't make sense please let me know and I will try to explain in a different manner. I have also posted the excel spreadsheet in that link above for your convenience. As always thanks for your time. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting help
Karl,
I would try this for the Green: =AND(V3=$E3,V3<=$F3,$B3<"X") and this for the grey =AND(V3=$E3,V3<=$F3,$B3="X") Both of these should be applied when cell V3 is the activecell - I'm not sure about your key date structure, since I don't download unknown spreadsheets. But I've assumed that they key dates are in the same row, as opposed to off by 10 (E3 vs. E13) HTH, Bernie MS Excel MVP wrote in message ups.com... Hello, I posted a question last night but see that I have one piece to finish my spreadsheet. Here it is... I have a Gantt Chart that I am developing in excel. Basically I have conditional formatting that compares the date range of a particular task to the actual date and thus fills in small excel squares showing visually how things are progressing in the timeline. It shows in little green squares When the task starts and when it is supposed to end. Here is a screenshot of what it looks like. http://download.shireinteractive.com/images So basically as you can see on row 31 I have the text grayed out because 'X" is in the field B31. I would like to apply that same grayed out effect to the green date range on the right in row31. Currently that date range code looks like this. =IF(AND(V$3=$E13,V$3<=$F13),1,"") There is then a conditional formatting applied to that cell telling it that if it equals "1" to make that cell green. Right now anything that is not in the date range is white, Anything that is in the date range is green. I want to add one more component, anything that is in the date range and has an "x" in the B column in that row, I want it to turn that green date range to Gray. Is this possible? If this doesn't make sense please let me know and I will try to explain in a different manner. I have also posted the excel spreadsheet in that link above for your convenience. As always thanks for your time. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting help
Condition 1 for Green:
=AND(W$3=$E31,W$3<=$F31) Condition 2 for Gray: =AND(AND(W$3=$E31,W$3<=$F31),B31="x") If you do not do any calculations on all the ones (1) that your current conditional formatting looks at, you can get rid of those formulas and just use the Conditional formatting above. Regards, Paul wrote in message ups.com... Hello, I posted a question last night but see that I have one piece to finish my spreadsheet. Here it is... I have a Gantt Chart that I am developing in excel. Basically I have conditional formatting that compares the date range of a particular task to the actual date and thus fills in small excel squares showing visually how things are progressing in the timeline. It shows in little green squares When the task starts and when it is supposed to end. Here is a screenshot of what it looks like. http://download.shireinteractive.com/images So basically as you can see on row 31 I have the text grayed out because 'X" is in the field B31. I would like to apply that same grayed out effect to the green date range on the right in row31. Currently that date range code looks like this. =IF(AND(V$3=$E13,V$3<=$F13),1,"") There is then a conditional formatting applied to that cell telling it that if it equals "1" to make that cell green. Right now anything that is not in the date range is white, Anything that is in the date range is green. I want to add one more component, anything that is in the date range and has an "x" in the B column in that row, I want it to turn that green date range to Gray. Is this possible? If this doesn't make sense please let me know and I will try to explain in a different manner. I have also posted the excel spreadsheet in that link above for your convenience. As always thanks for your time. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting help
Oops! That order should be reversed.
Condition 1 for Gray: =AND(AND(W$3=$E31,W$3<=$F31),B31="x") Condition 2 for Green: =AND(W$3=$E31,W$3<=$F31) "PCLIVE" wrote in message ... Condition 1 for Green: =AND(W$3=$E31,W$3<=$F31) Condition 2 for Gray: =AND(AND(W$3=$E31,W$3<=$F31),B31="x") If you do not do any calculations on all the ones (1) that your current conditional formatting looks at, you can get rid of those formulas and just use the Conditional formatting above. Regards, Paul wrote in message ups.com... Hello, I posted a question last night but see that I have one piece to finish my spreadsheet. Here it is... I have a Gantt Chart that I am developing in excel. Basically I have conditional formatting that compares the date range of a particular task to the actual date and thus fills in small excel squares showing visually how things are progressing in the timeline. It shows in little green squares When the task starts and when it is supposed to end. Here is a screenshot of what it looks like. http://download.shireinteractive.com/images So basically as you can see on row 31 I have the text grayed out because 'X" is in the field B31. I would like to apply that same grayed out effect to the green date range on the right in row31. Currently that date range code looks like this. =IF(AND(V$3=$E13,V$3<=$F13),1,"") There is then a conditional formatting applied to that cell telling it that if it equals "1" to make that cell green. Right now anything that is not in the date range is white, Anything that is in the date range is green. I want to add one more component, anything that is in the date range and has an "x" in the B column in that row, I want it to turn that green date range to Gray. Is this possible? If this doesn't make sense please let me know and I will try to explain in a different manner. I have also posted the excel spreadsheet in that link above for your convenience. As always thanks for your time. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting help
Hello Bob,
This works except for one thing. I only want the date range designated in columns E and F to be grayed over in the "green gantt chart area" but not in the rest of the row. That way we still see our Gantt chart but get to see the grayed areas as complete. The other cells out side that date range designated by E and F should be the white/no color background. Make sense? Thanks again for all your help. Bob Phillips wrote: I have updated your file to show how to do it. It is a zip file, as it was too big to upload it vanilla. Get it at :http://cjoint.com/?kupXBMVtGM -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hello, I posted a question last night but see that I have one piece to finish my spreadsheet. Here it is... I have a Gantt Chart that I am developing in excel. Basically I have conditional formatting that compares the date range of a particular task to the actual date and thus fills in small excel squares showing visually how things are progressing in the timeline. It shows in little green squares When the task starts and when it is supposed to end. Here is a screenshot of what it looks like. http://download.shireinteractive.com/images So basically as you can see on row 31 I have the text grayed out because 'X" is in the field B31. I would like to apply that same grayed out effect to the green date range on the right in row31. Currently that date range code looks like this. =IF(AND(V$3=$E13,V$3<=$F13),1,"") There is then a conditional formatting applied to that cell telling it that if it equals "1" to make that cell green. Right now anything that is not in the date range is white, Anything that is in the date range is green. I want to add one more component, anything that is in the date range and has an "x" in the B column in that row, I want it to turn that green date range to Gray. Is this possible? If this doesn't make sense please let me know and I will try to explain in a different manner. I have also posted the excel spreadsheet in that link above for your convenience. As always thanks for your time. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
conditional formatting help
I got it working!!!
Thanks again. I greatly appreciate it! wrote: Hello Bob, This works except for one thing. I only want the date range designated in columns E and F to be grayed over in the "green gantt chart area" but not in the rest of the row. That way we still see our Gantt chart but get to see the grayed areas as complete. The other cells out side that date range designated by E and F should be the white/no color background. Make sense? Thanks again for all your help. Bob Phillips wrote: I have updated your file to show how to do it. It is a zip file, as it was too big to upload it vanilla. Get it at :http://cjoint.com/?kupXBMVtGM -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... Hello, I posted a question last night but see that I have one piece to finish my spreadsheet. Here it is... I have a Gantt Chart that I am developing in excel. Basically I have conditional formatting that compares the date range of a particular task to the actual date and thus fills in small excel squares showing visually how things are progressing in the timeline. It shows in little green squares When the task starts and when it is supposed to end. Here is a screenshot of what it looks like. http://download.shireinteractive.com/images So basically as you can see on row 31 I have the text grayed out because 'X" is in the field B31. I would like to apply that same grayed out effect to the green date range on the right in row31. Currently that date range code looks like this. =IF(AND(V$3=$E13,V$3<=$F13),1,"") There is then a conditional formatting applied to that cell telling it that if it equals "1" to make that cell green. Right now anything that is not in the date range is white, Anything that is in the date range is green. I want to add one more component, anything that is in the date range and has an "x" in the B column in that row, I want it to turn that green date range to Gray. Is this possible? If this doesn't make sense please let me know and I will try to explain in a different manner. I have also posted the excel spreadsheet in that link above for your convenience. As always thanks for your time. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting a range with conditional formatting | Excel Worksheet Functions | |||
conditional formatting glitches | Excel Discussion (Misc queries) | |||
Keeping conditional formatting when sorting | Excel Discussion (Misc queries) | |||
conditional formatting | Excel Discussion (Misc queries) | |||
cannot use ISEVEN or ISODD functions in Conditional Formatting | Excel Worksheet Functions |