# Look up formula

#### paul43

##### New Member
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.

### Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

#### offthelip

##### Well-known Member
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

##### New Member
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

##### Well-known Member
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

##### New Member

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

##### Well-known Member
So how do you want the system to work??

#### offthelip

##### Well-known Member
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:
VBA Code:
``````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
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``````

Replies
0
Views
71
Replies
0
Views
56
Replies
4
Views
114
Replies
0
Views
34
Replies
5
Views
61

1,136,304
Messages
5,674,973
Members
419,537
Latest member
ucatchy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back