Auto-populate "Yes" "No" based on multiple criteria.

[jtmoore]

Hello,

I have a workbook with two worksheets.
Sheet 2 is a change log that as the name indicates logs all the changes that occur to a JB #.
Sheet 1 is a closed log that logs information about closed JB #'s.
On Sheet 1 there are three columns, B, C, D which track information such as 'was an extension granted', 'was the job ever late/over-due', etc. Currently, I have to manually enter Yes or No for each which requires a lot of time to look through sheet 2 and determine if a job meets any of those criteria.

Sheet 2 has all of this information for each job to determine this, but I'm not sure how to get excel to look for a specific JB # and then only look at the column "What Changed" for that job # to determine if it meets the criteria.

Thanks for any and all help in this, sample workbook is attached.

Sample Workbook.xlsx

[Ace_XL]

In B2
=IF(COUNTIFS(Sheet2!$A:$A,$A1,Sheet2!$C:$C,"Extension*"),"Yes","No")

In C2
=IF(COUNTIFS(Sheet2!$A:$A,$A1,Sheet2!$C:$C,"Late"),"Yes","No")

In D2
=IF(COUNTIFS(Sheet2!$A:$A,$A1,Sheet2!$C:$C,"Extension/Rejection"),"Yes","No")

Copy down

[jtmoore]

Thanks for your response.
I'm not sure if I broke it or if the formula does not work.
I copied them into their respective columns but they seem to give a yes no output based on, I'm not sure.
For instance, in the sample workbook. JB # 247, it was granted an extension, but this is not shown using the formulas.

Thanks!

[Ace_XL]

oops $A1 should be $A2 since Row 2 has you first JB#

[jtmoore]

Awesome Thank you!

I also realized when I copied it into my actual workbook, somehow the criteria 2 range was changed to something random.

Thanks for your help!