Formula Help

JulieB78

I have two sheets one the information is going to be fed into from a form lets call this "Training Completed" so when the info is in this is what it would look like

Then I want it to populate the next sheet

with a 1 if they have completed the training

So in this example it should look like the below if the formula is working correctly

I have tried ifs but it wasn't successful.

I would love any other suggestions of formula that can be used.

AravindGP

Hi @JulieB78

You can try the below formula. The curly brackets refer to cross sheet references which you can create by clicking on the "Reference another sheet" link while creating the formula.

=IFERROR(IF(INDEX(COLLECT({Training}, {Training}, [Column Name in sheet 2 with the name of the training]@row, {Employee}, "Mike Smith"), 1) = [Column Name in sheet 2 with the name of the training]@row, 1), "")

{Training} is the name of the reference I have given in the example for referencing the Training column in sheet 1

{Employee} is the name of the reference I have given in the example for referencing the Employee column in sheet 1

You will need to replace the name of the resource for Jenny long, Joe Bloggs, etc. in the formula for the columns in sheet 2 containing the names.