Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting In and Across Sheets
Thanks Bob.
I'm actually just referring to a cell rather than a range . . . The condition is basically: =AND(M$2=$E7,M$2<=$F7) where E7 and F7 each contain a date But if I try to use that condition to refer to e7 and f7 in a different sheet, I'm not allowed to do it . . . Therein lies my dilemma. "Bob Phillips" wrote: It is allowed, but any range that you refer to on another sheet must be referenced by way of a named range (InsertNameDefine...). -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jkl" wrote in message ... My version of Excel informs me that condtional formatting across worksheets is not allowed. Is that the case for the latest versions of Excel as well? If so, does anyone have a handy way of getting around this limitation? I am trying to use dates in one worksheet to allow me to generate a gantt chart in another worksheet. The best I can do at this point is copy the date info from the first sheet into the second sheet and then do conditional formatting within the second sheet. I'd just as soon avoid having the date columns in the second sheet since the gantt charts will display the dates. In addition, when doing the conditional formatting, the cells keep displaying the result (TRUE or FALSE). This makes the gantt chart look pretty silly. Any way I can tell Excel not to make the result visible but still provide the format requested (in this case, color shading). THANKS! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting In and Across Sheets
"jkl" wrote:
.. =AND(M$2=$E7,M$2<=$F7) where E7 and F7 each contain a date But if I try to use that condition to refer to e7 and f7 in a different sheet, I'm not allowed to do it . . . You could also try INDIRECT, something along these lines .. Assuming E7 and F7 are in Sheet2, use as the CF formula (in say, Sheet1): =AND(M$2=INDIRECT("'Sheet2'!E7"),M$2<=INDIRECT("' Sheet2'!F7"),INDIRECT("'Sheet2'!E7")<"",INDIRECT( "'Sheet2'!F7")<"") Above contains 2 additional checks to ensure that E7 and F7 in Sheet2 are not blank. These will help to prevent false indications caused by blank cells. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting In and Across Sheets
It doesn't matter, a range is a group of cells, and one cell is a range.
Name that cell on the other sheet, and refer to the name in the CF. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jkl" wrote in message ... Thanks Bob. I'm actually just referring to a cell rather than a range . . . The condition is basically: =AND(M$2=$E7,M$2<=$F7) where E7 and F7 each contain a date But if I try to use that condition to refer to e7 and f7 in a different sheet, I'm not allowed to do it . . . Therein lies my dilemma. "Bob Phillips" wrote: It is allowed, but any range that you refer to on another sheet must be referenced by way of a named range (InsertNameDefine...). -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jkl" wrote in message ... My version of Excel informs me that condtional formatting across worksheets is not allowed. Is that the case for the latest versions of Excel as well? If so, does anyone have a handy way of getting around this limitation? I am trying to use dates in one worksheet to allow me to generate a gantt chart in another worksheet. The best I can do at this point is copy the date info from the first sheet into the second sheet and then do conditional formatting within the second sheet. I'd just as soon avoid having the date columns in the second sheet since the gantt charts will display the dates. In addition, when doing the conditional formatting, the cells keep displaying the result (TRUE or FALSE). This makes the gantt chart look pretty silly. Any way I can tell Excel not to make the result visible but still provide the format requested (in this case, color shading). THANKS! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting In and Across Sheets
Thanks Bob!
"Bob Phillips" wrote: It doesn't matter, a range is a group of cells, and one cell is a range. Name that cell on the other sheet, and refer to the name in the CF. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jkl" wrote in message ... Thanks Bob. I'm actually just referring to a cell rather than a range . . . The condition is basically: =AND(M$2=$E7,M$2<=$F7) where E7 and F7 each contain a date But if I try to use that condition to refer to e7 and f7 in a different sheet, I'm not allowed to do it . . . Therein lies my dilemma. "Bob Phillips" wrote: It is allowed, but any range that you refer to on another sheet must be referenced by way of a named range (InsertNameDefine...). -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "jkl" wrote in message ... My version of Excel informs me that condtional formatting across worksheets is not allowed. Is that the case for the latest versions of Excel as well? If so, does anyone have a handy way of getting around this limitation? I am trying to use dates in one worksheet to allow me to generate a gantt chart in another worksheet. The best I can do at this point is copy the date info from the first sheet into the second sheet and then do conditional formatting within the second sheet. I'd just as soon avoid having the date columns in the second sheet since the gantt charts will display the dates. In addition, when doing the conditional formatting, the cells keep displaying the result (TRUE or FALSE). This makes the gantt chart look pretty silly. Any way I can tell Excel not to make the result visible but still provide the format requested (in this case, color shading). THANKS! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting In and Across Sheets
Thanks Max!
"Max" wrote: "jkl" wrote: .. =AND(M$2=$E7,M$2<=$F7) where E7 and F7 each contain a date But if I try to use that condition to refer to e7 and f7 in a different sheet, I'm not allowed to do it . . . You could also try INDIRECT, something along these lines .. Assuming E7 and F7 are in Sheet2, use as the CF formula (in say, Sheet1): =AND(M$2=INDIRECT("'Sheet2'!E7"),M$2<=INDIRECT("' Sheet2'!F7"),INDIRECT("'Sheet2'!E7")<"",INDIRECT( "'Sheet2'!F7")<"") Above contains 2 additional checks to ensure that E7 and F7 in Sheet2 are not blank. These will help to prevent false indications caused by blank cells. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting In and Across Sheets
Thanks for posting back.
Trust you got it going there .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jkl" wrote in message ... Thanks Max! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|