Making a training registry

Drew Wort

So i am attempting to create a sheet where an employee would fill out a form and check into the training. This will go onto a sheet called "Hicksville Training Sign In" They fill out the form and it populates the rows.

Then I want the form to match two variables into the sheet called "EE Matrix" I want it to Match a column on both sheets called "Employee ID" and place the date of the training from "Hicksville Training Sign In" Into the cell under the column with the training title.

Take the date from picture 2 match it to the employee ID column in Picture 1 and fill the date in the column for picture 1 in the proper column matched in picture 2 to the training title column

Paul Newcome

You would use an INDEX/COLLECT similar to:

=INDEX(COLLECT({Form Sheet Date Column}, {Form Sheet ID Column}, @cell = [ID Column Name]@row, {Form Sheet Training Title Column}, @cell = "Training Title"), 1)

Paul Newcome

I always use an IFERROR for this, but I wait to add it until we can make sure the base formula is working correctly in case we need to do any trouble shooting.

=IFERROR(original_formula, "")

Paul Newcome

You would use an INDEX/COLLECT similar to:

=INDEX(COLLECT({Form Sheet Date Column}, {Form Sheet ID Column}, @cell = [ID Column Name]@row, {Form Sheet Training Title Column}, @cell = "Training Title"), 1)

Drew Wort

ahhhh you are awesome!

Anyway i can make the cells say nothing instead of invalid value when there is no date?

Paul Newcome

I always use an IFERROR for this, but I wait to add it until we can make sure the base formula is working correctly in case we need to do any trouble shooting.

=IFERROR(original_formula, "")

Drew Wort

=IFERROR(INDEX(COLLECT({Hicksville Training Sign In Range 3}, {Hicksville Training Sign In Range 4}, @cell = [Employee ID]@row, {Hicksville Training Sign In Range 2}, @cell = "Crane Training"), 1, "")

PERFECT

Thank you so much!

Paul Newcome

Happy to help. 👍️