Look for a value

[JazzzyJo]

I have this table

<TABLE style="WIDTH: 107pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=142 border=0><COLGROUP><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><TBODY><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 107pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #bfbfbf" align=right width=142 height=21></TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #bfbfbf" height=21>January 1, 2011</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>January 1, 2012</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>January 1, 2013</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>January 1, 2014</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #bfbfbf" align=right height=22>August 16, 2011</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2015</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2016</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2017</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2018</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2019</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2020</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #bfbfbf" height=21>October 5, 2021</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #bfbfbf" height=22>October 5, 2022</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #bfbfbf" height=21>October 5, 2023</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2024</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #bfbfbf" height=22></TD></TR></TBODY></TABLE>

I'm looking for a formula that goes in the last cell. I want that formula to return blank if August 16, 2011 is found in the table above. Something like this

A17=if(august 16,2011 is found in A1:A16,"",august 16,2011)

Thx

 

[jim may]

In A17 enter

=IF(COUNTIF(A2:A16,DATEVALUE("8/16/2011"))>0,"",DATEVALUE("8/16/2011"))

 

[JazzzyJo]

Make sense. Sweet. Thx

 

[JazzzyJo]

Thought it would have work when I saw it but when I apply it, it doesnt work.

Here the formula =IF(COUNTIF(ME9:ME30,Horaire!$S$2)>0,"",Horaire!$S$2)

where Horaire!$S$2 is a date serial number

The result I get is 0 which doesnt make any sense. In S2 now I have the value 40771 (august 16,2011).

I get this

<TABLE style="WIDTH: 107pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=142 border=0><COLGROUP><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=142><TBODY><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl67 id=td_post_2832164 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 107pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: #92d050" width=142 height=20> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20> </TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #bfbfbf" align=right height=21>August 16, 2011</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #bfbfbf" height=21>January 1, 2011</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>January 1, 2012</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>January 1, 2013</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>January 1, 2014</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #bfbfbf" align=right height=22>August 16, 2011</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2015</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2016</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2017</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2018</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2019</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2020</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #bfbfbf" height=21>October 5, 2021</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: #bfbfbf" height=22>October 5, 2022</TD></TR><TR style="HEIGHT: 15.75pt; mso-height-source: userset" height=21><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: #bfbfbf" height=21>October 5, 2023</TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #bfbfbf" height=20>October 5, 2024</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" align=right height=22>0

<!-- / message --><!-- sig --></TD></TR></TBODY></TABLE>
And I have blue arrows going from a cell to the other cell down in everyone of them.

Whats happening.

 

[Misca]

Try

Excel Workbook
	A	B
15	Dates	Text
16	5.10.2024	October 5, 2024
17
Sheet

Both formulas in the example are looking for today's date but the difference is how the list values are entered:

If they're dates the first one should work no matter how the cells are formatted but if they're entered as text and you're trying to find a date from text, you're going to have to use the TEXT-function to change your numeric value to text first.

 

[JazzzyJo]

Thx I'll try that one.

 

[JazzzyJo]

Not working either. It's giving me a circular error. The value of A1 depends on A2 and the value of A2 depends of A3 and the value of A16 depend on the value of A17. I'll try to figure something out with what you gave me.

Thx

Let me know if you have an idea to go around that.