Filtering Dropdown lists

[mj2019]

Hey folks,

Wonder if you can help me out? I've created a spreadsheet that tracks utilisation of engineers in terms of various zones, pay rates, number of days etc. These engineers are split into different levels [supervisor and engineer] and it does the usual totalling up for various different periods and reports.

I have 2 x sheets, one with all the collation of data [names/total days/days per month/quarter etc] and the other is the actual work movements as they happen [job number, engineer, discipline, start date, expected duration, finish date, monetary value] and it Gantts each movement onto a year calendar to boot.

Whilst I have managed to create dependent drop down lists [thank you youtube videos!] I'm struggling to amend my drop downs to show me available personnel -- that is people who are no on a job or scheduled on a job greater than/equal to todays date -- you can't be in two places at once....right?

I thought nested IFs would work, such as -- {=IF('2019'!$F$24:$F$241=Analysis!$A4,IF(MAX('2019'!$J$24:$J$241)>TODAY(),"No",IF(MAX('2019'!$K$24:$K$241)<MAX('2019'!$J$24:$J$241),"Yes","No")))}
F24:F241 -- is the name of the engineers in a great long list
A4 - the specific name of the engineer that I want to compare in F24:f241 to see if he's on a job or scheduled on a job
J24:J241 -- start dates from the raw data for each engineer movement
K24:241 -- finish dates from the raw data for each engineer movement -- which is a calculation of (start date + duration columns - 1) -- 07/05/19 + 8days = 14/05/19

In a nutshell, I want to be forecasting and scheduling up & coming works, so that available engineers are utilised. Thus, I want to look up the raw data to see if JOE BLOGGS is either on a job or scheduled for a future job, so that he would/wouldn't be available for the new job that I am creating the movement for.

Make sense...hopefully?

Any help appreciated or different ways to structure my sheets to make the above principal achievable

Thanks
mj

[Pepe Le Mokko]

Title much more descriptive. Thx
(although the original one was funnier )

[Pepe Le Mokko]

Might I also suggest ( to get the ball rolling)
to attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.