Results 1 to 9 of 9
Thread: formula help (excel2003)

20070405, 17:02 #1
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
formula help (excel2003)
hi all,
I need a formula that will flaq "planned" in coulmn d sheet1 if there is a match between name&numb in sheet2 with name&numb in sheet1 and not planned flaq only if date value in sheet1 is 2007? attached book has a sample of the data and the expected results.
regards,TIA
dubdub

20070405, 17:29 #2
 Join Date
 Mar 2001
 Location
 Springfield, Ohio, USA
 Posts
 2,136
 Thanks
 0
 Thanked 2 Times in 2 Posts
Re: formula help (excel2003)
Formula for D2 is
<pre>=IF(YEAR(C2)=2007,IF(A2 & B2=Sheet2!A2 & Sheet2!B2,"planned","not planned"),"")</pre>
<font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
<small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

20070405, 18:44 #3
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: formula help (excel2003)
many thanks SammyB. what would be the array formula that will give the result in the attached book.
dubdubTIA
dubdub

20070405, 18:58 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: formula help (excel2003)
Please explain why you expect something different than SammyB's formula yields.
BTW, two of the dates on Sheet1 are not valid  they contain a double slash //.

20070405, 19:22 #5
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: formula help (excel2003)
hi HansV,
i have included an explination for the expected results.
regards,
dubdubTIA
dubdub

20070405, 19:31 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: formula help (excel2003)
In row 2 on Sheet1, name = "sma" and numb = 1. This combination occurs on Sheet2 in row 2, and the year is 2007.
In row 3 on Sheet1, name = "hana" and numb = 2. This combination occurs on Sheet2 in row 8, and the year is 2007.
Why should the first one be "planned" and the second one "not planned"? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
PS Your workbook contains 3 empty modules. You shouldn't include modules in an attachment unless they are essential for the question.

20070405, 19:57 #7
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: formula help (excel2003)
hansv,
both should be planned as i showed in the attached file expected results column, but applying SammyB's formula it assumes the data in both sheets have the same order, which is not.
see what i am after is a two criteria condition, one matching sheet 2 name& numb with name&numb in sheet 1 if match exist then it will be "planned", next for all other name and numb in sheet 1 which are not "planned" from the previous step (one) but they are 2007 i want them to be "not planned".
regards,TIA
dubdub

20070405, 20:03 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: formula help (excel2003)
Oops, sorry, I switched the expected result with that of the formula. Try this array formula in D2 (confirm with Ctrl+Shift+Enter):
=IF(YEAR(C2)=2007,IF(ISERROR(MATCH(A2&B2,Sheet2!$A $2:$A$9&Sheet2!$B$2:$B$9,0)),"not planned","planned"),"")
You can fill down this formula.

20070405, 20:16 #9
 Join Date
 Jul 2005
 Location
 Bahrain
 Posts
 376
 Thanks
 1
 Thanked 0 Times in 0 Posts
Re: formula help (excel2003)
Many thanks hansV.
dubdubTIA
dubdub