Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating with 3 conditions
I am using Excel 2003. I have the following conditions, but can not get the
result I want for Condition 3. I have tried various ways without success. Maybe what I am trying to do is not possible, but if it is can be done can someone help? Condition 1: cell value is equal to =$G$37 - no format set Condition 2: formula is =istext($G$37) - format set to color cell yellow with bottom cell underlined (this works) Condition 3: formula is =not(isnumber(G38)) - I want no cell color underline if text is entered in cell g38. I believe this event won't occur until the cell is exited. TIA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating with 3 conditions
Maybe absolute reference instead of relative reference?
=istext($G$38) Also, the 2nd and 3rd condition could both be true at the same time. On my machine, if both were true it formatted according to the second condition. "Jan" wrote: I am using Excel 2003. I have the following conditions, but can not get the result I want for Condition 3. I have tried various ways without success. Maybe what I am trying to do is not possible, but if it is can be done can someone help? Condition 1: cell value is equal to =$G$37 - no format set Condition 2: formula is =istext($G$37) - format set to color cell yellow with bottom cell underlined (this works) Condition 3: formula is =not(isnumber(G38)) - I want no cell color underline if text is entered in cell g38. I believe this event won't occur until the cell is exited. TIA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating with 3 conditions
Changing to absolute reference did work. I see what you mean about condition
2 and 3. I basically want to achieve the following in cell G38. If G37 is empty, no color or formatiing in G38. If G37 has text, format g38 with color, etc. But if both G37 and g38 have text, format the cell without color. "JMB" wrote: Maybe absolute reference instead of relative reference? =istext($G$38) Also, the 2nd and 3rd condition could both be true at the same time. On my machine, if both were true it formatted according to the second condition. "Jan" wrote: I am using Excel 2003. I have the following conditions, but can not get the result I want for Condition 3. I have tried various ways without success. Maybe what I am trying to do is not possible, but if it is can be done can someone help? Condition 1: cell value is equal to =$G$37 - no format set Condition 2: formula is =istext($G$37) - format set to color cell yellow with bottom cell underlined (this works) Condition 3: formula is =not(isnumber(G38)) - I want no cell color underline if text is entered in cell g38. I believe this event won't occur until the cell is exited. TIA |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating with 3 conditions
Never mind, I finally got it to work. In case anyone may need to do the same
thing, here is how it was done. Condition 1 = formula is =AND(istext($g$37),NOT(istext($G$38))) Condition 2 = cell value is not equal to =ISTEXT($G$37)*ISTEXT(($G$38)) "JMB" wrote: Maybe absolute reference instead of relative reference? =istext($G$38) Also, the 2nd and 3rd condition could both be true at the same time. On my machine, if both were true it formatted according to the second condition. "Jan" wrote: I am using Excel 2003. I have the following conditions, but can not get the result I want for Condition 3. I have tried various ways without success. Maybe what I am trying to do is not possible, but if it is can be done can someone help? Condition 1: cell value is equal to =$G$37 - no format set Condition 2: formula is =istext($G$37) - format set to color cell yellow with bottom cell underlined (this works) Condition 3: formula is =not(isnumber(G38)) - I want no cell color underline if text is entered in cell g38. I believe this event won't occur until the cell is exited. TIA |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formating with 3 conditions
when you say "text" I am assuming you mean if G37 contains numbers Condition
2 and Condition 3 will not be met (and that is your intent). Also, note that if the cell is truly empty, ISTEXT returns FALSE (as does ISNUMBER) whereas if it contains the null string (ie "") ISTEXT returns TRUE even though the cell appears empty. Condition 1: =$G$37="" Condition 2 - still vague how this will differ from condition 3. If G37 has text and G38 has a number? =AND(ISTEXT($G$37), ISNUMBER($G$38)) or if G37 has text and G38 is a number or is empty? =AND(ISTEXT($G$37), OR(ISNUMBER($G$38),$G$38="")) Condition 3: =AND(ISTEXT($G$G7, ISTEXT($G$38)) Or you could try changing the order of Condition2 and Condition3: Condition 1: =$G$37="" Condition 2: =AND(ISTEXT($G$37, ISTEXT($G$38)) Condition 3: =ISTEXT($G$37) "Jan" wrote: Changing to absolute reference did work. I see what you mean about condition 2 and 3. I basically want to achieve the following in cell G38. If G37 is empty, no color or formatiing in G38. If G37 has text, format g38 with color, etc. But if both G37 and g38 have text, format the cell without color. "JMB" wrote: Maybe absolute reference instead of relative reference? =istext($G$38) Also, the 2nd and 3rd condition could both be true at the same time. On my machine, if both were true it formatted according to the second condition. "Jan" wrote: I am using Excel 2003. I have the following conditions, but can not get the result I want for Condition 3. I have tried various ways without success. Maybe what I am trying to do is not possible, but if it is can be done can someone help? Condition 1: cell value is equal to =$G$37 - no format set Condition 2: formula is =istext($G$37) - format set to color cell yellow with bottom cell underlined (this works) Condition 3: formula is =not(isnumber(G38)) - I want no cell color underline if text is entered in cell g38. I believe this event won't occur until the cell is exited. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i copy conditional formating formulas from 1 row to rest | Excel Discussion (Misc queries) | |||
conditional formating - wildcards | Excel Worksheet Functions | |||
Conditional Formating 4 conditions | Excel Discussion (Misc queries) | |||
Conditional Formating | Excel Discussion (Misc queries) | |||
more than 3 conditional formating in excel | Excel Discussion (Misc queries) |