Solved: If Contains

[khalid79m]

Hi I have a list of file names in column a. eg of file names

Cairo_CM_DDA_Micheal Smith_RSSv1_12_2008.xls
London_CM_DDA_Joe Smith_RSSv1_12_2008.xls
Manchester_CM_DDA_Arnold John_RSSv1_12_2008.xls
NewYork_CM_DDA_Liam Noel_RSSv1_12_2008.xls
Dehli_CM_DDA_Micheal Jordan _RSSv1_12_2008.xls
Oxford_CM_DDA_Andrew Netto_RSSv1_12_2008.xls
Cairo_CM_DDA_Micheal Smith_RSSv1_11_2008.xls
London_CM_DDA_Joe Smith_RSSv1_11_2008.xls
Manchester_CM_DDA_Arnold John_RSSv1_11_2008.xls
NewYork_CM_DDA_Liam Noel_RSSv1_11_2008.xls
Dehli_CM_DDA_Micheal Jordan _RSSv1_11_2008.xls
Oxford_CM_DDA_Andrew Netto_RSSv1_11_2008.xls

For each file name in column A I want to put in column B "PASS" or "FAIL",

For the file to pass it must

contains the word in Sheet Control_Panel $A$1 & $B$2

please note curently in Control_Panel $A$1 = 12 & $B$2 = 2008 so the pass results would be

Cairo_CM_DDA_Micheal Smith_RSSv1_12_2008.xls
London_CM_DDA_Joe Smith_RSSv1_12_2008.xls
Manchester_CM_DDA_Arnold John_RSSv1_12_2008.xls
NewYork_CM_DDA_Liam Noel_RSSv1_12_2008.xls
Dehli_CM_DDA_Micheal Jordan _RSSv1_12_2008.xls
Oxford_CM_DDA_Andrew Netto_RSSv1_12_2008.xls

I hope this is clear, please contact me if you have any question, im really struggling with this one...:help

[IkEcht]

And your question is what exactly?

But to answer your question more accurately, how about:
if(right(a1;11)=Control_Panel!$a$1 & "_" & Control_panel!$b$2 & ".xls";"Pass";"Fail")
and filling it down of course.

[IkEcht]

Oh and watch it, the formule only works the whole year around if you keep on using double digits for the months ( I presume), so 01 for january instead of just 1.

[khalid79m]

Sorry but the systems guys here pre program the files and it is 1 , 2,3,4,5,6,7,8,9,10,11,12 not 01,02,03 etc.. how can I do this ?

[IkEcht]

Quite simple as there still is the "_" preceding the months. From month 1 to 9 make sure Control_Panel!A1 contains "_1" to "_9" the previous given solution will still work.

[khalid79m]

done it , i will post formula in a few minutes

[Bob Phillips]

=IF(AND(ISNUMBER(SEARCH("_"&Control_Panel!$A$1,A1)),ISNUMBER(SEARCH("_"&Control_Panel!$B$2,A1))),"Pass","Fail")

[khalid79m]

Thanks EL XLD your code was if statement was much less complicated than mine thanks.