Look up formula

[paul43]

I have a complicated, for me, question. I have a 2 sheet(job list and long term schedule) excel file that i use to track and schedule 16 crews on jobs. One sheet is a job list, that contains a job number for each job and a column that i put the first date it shows up on schedule. The second sheet is a year long schedule that I use to schedule my crews. Along the top row is the date and then 16 crews are listed down the left side. I then Put job number and location each day for each crew. What i want to do if create a formula on my job list that will search the long term schedule sheet for the 1st time a job number shows up and then reference that date.

Does that make sense? Ive tried many formulas, then came upon this site hoping to find some help.

 

[offthelip]

Where or how do you define which crew is used on which job? Until you answer this question it is impossible to automate a solution

 

[paul43]

Where or how do you define which crew is used on which job? Until you answer this question it is impossible to automate a solution

The jobs are assigned by location typically. the first image is of my job list and the second is the year long schedule. the two images are of each sheet in the file. The blue column that say 1st day on schedule is where I would like to auto populate the date from the second image. The second image is the year long schedule. I want the formula to search for that job number then reference the date of the first time it shows on the schedule.

 

[offthelip]

That hasn't answered the question, How do you know that Crew 1 is working on 32446? and crew 2 is working on 32628? an automatic system needs to know which job is allocated to which crew

 

[paul43]

That hasn't answered the question, How do you know that Crew 1 is working on 32446? and crew 2 is working on 32628? an automatic system needs to know which job is allocated to which crew

Me and another person schedule a crew on a job manually. But we are constantly reworking the schedule due to weather or other issues. So Im looking for the system to be able to tell me what date a job first shows up on the schedule in case I forget to change the date manually.

 

[offthelip]

So how do you want the system to work??

 

[offthelip]

You could try putting this code into the worksheet code for the Schedule sheet. You will probably have to change the name of the JobList worksheets to be exactly what your name is:
This code will throw a message box if you enter a job number in column before the start date:

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("JobList")
 lastrow = .Cells(Rows.Count, "C").End(xlUp).Row
 jobar = .Range(.Cells(1, 3), .Cells(lastrow, 3))
 datar = .Range(.Cells(1, 14), .Cells(lastrow, 14))
  For i = 1 To lastrow
   If Target.Value = jobar(i, 1) Then
    coldate = Target.Column
    headate = Cells(7, coldate)
     If headate < datar(i, 1) Then
      MsgBox ("Job Date is " & datar(i, 1) & " which is after the heading date " & headdate)
     End If
    Exit For
   End If
  Next i
End With
End Sub