Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
I setup the following formula in conditional formatting to turn the cell
yellow if the amount listed in cell B25 is not found in column T. I then also added the beginning part of the formula to not turn yellow if the date listed in cell A1 is a day in the futu =0=IF(A1=TODAY(),1,IF(ISNA(VLOOKUP(B25,T2:T1500,1 ,0)),0,1)) I want to add one more condition to the forumla that says if the amount listed in cell B25 is 0, don't turn the cell yellow. How can I tweak the formula listed above? Right now the cell is turning yellow because a 0 is in cell B25, but not found in column T. Any help would be appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Then, just make a condition of =B25=0 as yoru condition 1 with no format
applied and make the below your 2nd condiiton. =OR(A1=TODAY(),ISNA(VLOOKUP(B25,T2:T1500,1,0))) "Brian" wrote: I setup the following formula in conditional formatting to turn the cell yellow if the amount listed in cell B25 is not found in column T. I then also added the beginning part of the formula to not turn yellow if the date listed in cell A1 is a day in the futu =0=IF(A1=TODAY(),1,IF(ISNA(VLOOKUP(B25,T2:T1500,1 ,0)),0,1)) I want to add one more condition to the forumla that says if the amount listed in cell B25 is 0, don't turn the cell yellow. How can I tweak the formula listed above? Right now the cell is turning yellow because a 0 is in cell B25, but not found in column T. Any help would be appreciated! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Paste the below as CF formula
=AND(A1<TODAY(),B25<0,COUNTIF(T:T,B25)=0) If this post helps click Yes --------------- Jacob Skaria "Brian" wrote: I setup the following formula in conditional formatting to turn the cell yellow if the amount listed in cell B25 is not found in column T. I then also added the beginning part of the formula to not turn yellow if the date listed in cell A1 is a day in the futu =0=IF(A1=TODAY(),1,IF(ISNA(VLOOKUP(B25,T2:T1500,1 ,0)),0,1)) I want to add one more condition to the forumla that says if the amount listed in cell B25 is 0, don't turn the cell yellow. How can I tweak the formula listed above? Right now the cell is turning yellow because a 0 is in cell B25, but not found in column T. Any help would be appreciated! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Conditional formats want formulas that resolve to true or false. True means
apply the format. False means no format. To that end you can use AND very effectively. =and(A1<TODAY(), B25<0, ISNA(VLOOKUP(B25,T2:T1500,1,0))) I think my boolean is correct. -- HTH... Jim Thomlinson "Brian" wrote: I setup the following formula in conditional formatting to turn the cell yellow if the amount listed in cell B25 is not found in column T. I then also added the beginning part of the formula to not turn yellow if the date listed in cell A1 is a day in the futu =0=IF(A1=TODAY(),1,IF(ISNA(VLOOKUP(B25,T2:T1500,1 ,0)),0,1)) I want to add one more condition to the forumla that says if the amount listed in cell B25 is 0, don't turn the cell yellow. How can I tweak the formula listed above? Right now the cell is turning yellow because a 0 is in cell B25, but not found in column T. Any help would be appreciated! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Note that CountIf will treat strings and numbers the same. To that end
countif can find the value while vlookup will evaluate to NA. For example if 123 is a string and you try to find it in a group of numbers it will return NA. Countif however would find it. That is a feature that I leverage in my reference functions such as VLookup or match but I am not sure that it is appropriate here. That is for the OP to decide... -- HTH... Jim Thomlinson "Jacob Skaria" wrote: Paste the below as CF formula =AND(A1<TODAY(),B25<0,COUNTIF(T:T,B25)=0) If this post helps click Yes --------------- Jacob Skaria "Brian" wrote: I setup the following formula in conditional formatting to turn the cell yellow if the amount listed in cell B25 is not found in column T. I then also added the beginning part of the formula to not turn yellow if the date listed in cell A1 is a day in the futu =0=IF(A1=TODAY(),1,IF(ISNA(VLOOKUP(B25,T2:T1500,1 ,0)),0,1)) I want to add one more condition to the forumla that says if the amount listed in cell B25 is 0, don't turn the cell yellow. How can I tweak the formula listed above? Right now the cell is turning yellow because a 0 is in cell B25, but not found in column T. Any help would be appreciated! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional Formatting
Jim, thanks for explaining. Appreciate that..
When I ready the post initially it seemed to me that column T is 'column of amounts'. If this post helps click Yes --------------- Jacob Skaria "Jim Thomlinson" wrote: Note that CountIf will treat strings and numbers the same. To that end countif can find the value while vlookup will evaluate to NA. For example if 123 is a string and you try to find it in a group of numbers it will return NA. Countif however would find it. That is a feature that I leverage in my reference functions such as VLookup or match but I am not sure that it is appropriate here. That is for the OP to decide... -- HTH... Jim Thomlinson "Jacob Skaria" wrote: Paste the below as CF formula =AND(A1<TODAY(),B25<0,COUNTIF(T:T,B25)=0) If this post helps click Yes --------------- Jacob Skaria "Brian" wrote: I setup the following formula in conditional formatting to turn the cell yellow if the amount listed in cell B25 is not found in column T. I then also added the beginning part of the formula to not turn yellow if the date listed in cell A1 is a day in the futu =0=IF(A1=TODAY(),1,IF(ISNA(VLOOKUP(B25,T2:T1500,1 ,0)),0,1)) I want to add one more condition to the forumla that says if the amount listed in cell B25 is 0, don't turn the cell yellow. How can I tweak the formula listed above? Right now the cell is turning yellow because a 0 is in cell B25, but not found in column T. Any help would be appreciated! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions |