Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    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,
    Attached Files Attached Files
    TIA
    dubdub

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    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>

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    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.

    dubdub
    Attached Files Attached Files
    TIA
    dubdub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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 //.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formula help (excel2003)

    hi HansV,
    i have included an explination for the expected results.
    regards,
    dubdub
    Attached Files Attached Files
    TIA
    dubdub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    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

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: formula help (excel2003)

    Many thanks hansV.

    dubdub
    TIA
    dubdub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •