Keeping Indexed and Non-Indexed Columns on the same Row

Leeweber

I'm making a training matrix for my company. Source sheet is the "matrix" that will list all employees and their required trainings:

*RowID is auto-numbered column
*RowNo formula is =MATCH(RowID@row, RowID:RowID, 0)
*E1 (helper column) formula is =COUNTIFS([Employee1]:[Employee1], [Employee1]@row, RowNo:RowNo, @cell <= RowID@row, [Employee1]:[Employee1], 1)

There will be a destination sheet per employee with index/match showing only what they need to be trained on

*Cross sheet references are as follows {DocCat}, {DocID}, {DocTitle}, {RevNo}, {VersionDate}, {TrainFreq}, {Employee}, {Helper}
*RowID is auto-numbered column
*RowNo formula is =MATCH(RowID@row, RowID:RowID, 0)
*Training Complete formula is =IF(NOT(ISBLANK([Training Date]@row)), "Yes", "No")
*Each column uses the following formula, only changing the referenced column =IFERROR(INDEX(COLLECT({DocCat}, {Employee}, 1, {Helper}, RowNo@row), 1), "")

My issue is if I add a training, it will be inserted in the order it appears on the matrix, shifting the indexed values down but NOT the respective training date. My example below shows a training date entered for Cert-003, but when adding Cert-002 as a training requirement, Cert-003 and 004 are moved down and the training date now appears in line with Cert-002 rather than staying with Cert-003.

Any suggestions would be greatly appreciated!

Paul Newcome

You would need to keep a separate list for each employee that only contains finished certs and their training dates so that you can use an INDEX/MATCH to pull the appropriate training date over in relation to the cert regardless of row number.

Paul Newcome

You would need to keep a separate list for each employee that only contains finished certs and their training dates so that you can use an INDEX/MATCH to pull the appropriate training date over in relation to the cert regardless of row number.