Trying to get this cross sheet formula to work, pro desk and 24/7 support haven't got back

Seth D

Please Help!

Sheet 1 (Equipment Inspection Sheet)

Column 1 (Unit # multidropdown),

Column 2 (Employee contact),

Column 3 (Competency and Ticket check checkbox)

Sheet 2 (Equipment Log)

Column 1 (Unit # multidropdown),

Column 2 (Req. Competency multidropdown)

Sheet 3 (Competency Log) 

Column 1 (Employee contact),

Column 2 (Competency multidropdown)

Sheet 4 (Training Log)

Column 1 (Employee contact), 

Column 2 (Ticket multidropdown)

 

A form used for inspections has the user input their name (already generated into contact list) and the unit # used. Other information is included but not pertinent. For the purpose of this example there will only by 1 input named Insp-001 on row 1 of sheet 1.

In Sheet 2 – A specific unit # will have no more than 1 competency and 1 ticket associated

Sheet 3 & Sheet 4 – Column 2, This will contain multiple instances of the same dropdown option but only one instance where it is in the same row as the employee in question

                                                   

Eg. Insp-001: Unit # - 904572  Employee – Junior Wilson  Required Competency – Excavator Required Training – Ground Disturbance

*Note, not all unit numbers require a competency or training*

Objective: To have the checkbox marked check in Sheet 1, Column 3, Row 1 if the employee has the required competency and ticket for the unit # used

 

The unit # is checked against the equipment log for a match, this returns the required competency – excavator, and required Ticket – Ground Disturbance.

The Competency Log needs to be checked for any rows containing Dropdown Junior Watson in the same row as Excavator.

The Training Log needs to be checked for any rows containing Junior Watson and Ground Disturbance. 

Paul Newcome

Are you able to provide screenshots for reference?