Look up formula

paul43

New Member
Joined
May 12, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 23, 2017
Messages
1,898
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 12, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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.

1620920821419.png


1620920834317.png
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,898
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
May 12, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Dec 23, 2017
Messages
1,898
Office Version
  1. 2010
Platform
  1. Windows
So how do you want the system to work??
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,898
Office Version
  1. 2010
Platform
  1. Windows
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
    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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top