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!

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!

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!

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!

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!

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!