Pulling date from 'Training record' and populating the date in a cell in another sheet

AJ Barry

So my question is complex and I am doubting it can be done.

I have a training matrix with 'Item' (Unique for each item) as rows and People as Columns.

In the Person column, the date is entered when the person is trained to an Item.

What I want to do now is to set up a training record smartsheet where the item and date trained is the row and the Trainees are the columns. The trainee columns are contact cells and will contain email addresses of the trainees and be populated using a Smarthsheet form.

So I wanted to take the Date that the item was trained for all trainees and automatically get it entered in the training matrix.

This sounds complex and I feel based on the fact that there are not enough common factors across the sheets it may not be possible.

Currently dates of training completed are manually entered into the training matrix by the trainees and there is no digital training record.

Future state for me is to capture training on a Smartsheet form.

The objective would be to then automatically take the date that the trainee was trained and enter that date in the cell of the common and unique 'Item' and the column corresponding with trainee.

Currently trainee columns are named 'John Doe' for example.

In the training record the columns will be 'Trainee 1' , 'Trainee 2' etc and the cell will contain the email address of the trainee. So only common link (from a look up perspective) is the 'Item'. This would be common in both sheets.

This is complex and maybe can be simplified by renaming columns but the sheets serve 2 different use cases so I am not sure if they can effectively cross link how I want.

Referring to the image, I want the info from 'Date' column in the training record (ie 19/06/25) to automatically get put into the 'AJ Barry' column for the item 'OP0012'. And overwrite the existing date of 17/02/25 (currently entered manually)

Training matrix_Training record column image.png

Naeem Ejaz

You cannot write a formula in column “AJ Barry” that says:
“If the Training Log says AJ Barry trained on OP0012 on June 19, write that date here”
— because formulas can’t dynamically pick columns.

jmyzk_cloudsmart_jp

Hi @AJ Barry

I wanted to share a solution that helps bridge two training-related Smartsheets using formulas. This approach automates how trainee data (especially email addresses) are matched back to names and positions in a training matrix. Here's the setup and how it works.

Key Challenges:

• Column mismatch:
  The Training Matrix sheet uses people’s names as column headers (e.g., "AJ Barry"), while the Training Record sheet logs email addresses (e.g., "ajbarry@email .com").
• No direct common key:
  Since names and emails are on different sheets, there’s no natural lookup key to connect them.
• Cross-sheet lookup complexity:
  Smartsheet doesn’t natively support multi-step lookups (e.g., from email → name → column), making this type of dynamic mapping hard.

https://app.smartsheet.com/b/publish?EQBCT=a8a586c3cb474308a98435967503f8a6

Possible Solution: Introduce a Column Position Lookup Helper Sheet

To bridge the data between the two sheets, I created a lookup sheet with:

• Name (e.g., "AJ Barry")
• Email (e.g., "ajbarry@email .com")
• Column Position — the number matching their position in the matrix row (e.g., 2 for "AJ Barry", 3 for "Jamie Lee", etc.)

https://app.smartsheet.com/b/publish?EQBCT=e21d898a24b44efd89a5e61b20494e92

This allowed us to reverse-match email → name → matrix column number for formula-based automation.

Sheets Involved

1. Training Matrix

• Rows = Item (e.g., OP0012)
• Columns = Trainee names (e.g., AJ Barry, Jamie Lee, Juni, etc.)
• Each cell = Date that person was trained on the item
• Additional columns:
  • JOIN — Combines training dates across all users
  • Date — Extracts latest valid date from JOIN
  • Date Position — Identifies which person the latest date belongs to

2. Training Record

• Captures formal log of trainings
• Columns:
  • Item, Date, Name, Email, and Trainee 1–5 columns to hold emails
  • Data is populated based on the matrix sheet using formulas

https://app.smartsheet.com/b/publish?EQBCT=553d4778b36e4ea49a6eed7da2ef5d79

3. Name–Email Lookup Sheet

• Contains:
  • Name
  • Email
  • Column Position (e.g., 2 for "AJ Barry", 3 for "Jamie Lee", etc.)

Formulas Used

In the Training Matrix:

JOIN column:

=JOIN([AJ Barry]@row :App@row )

Joins all trainee date cells into one string for parsing.

Date column:

IF(ISBLANK(JOIN@row ), "", DATE(VALUE("20" + RIGHT(JOIN@row , 2)), VALUE(LEFT(JOIN@row , 2)), VALUE(MID(JOIN@row , 4, 2))))

Extracts the latest valid date from the JOIN string.

Date Position column:

=IF(ISDATE(Date@row ), IF(INDEX(Item@row :JOIN@row , 1, 2) = Date@row , 2, IF(INDEX(Item@row :JOIN@row , 1, 3) = Date@row , 3, IF(INDEX(Item@row :JOIN@row , 1, 4) = Date@row , 4, IF(INDEX(Item@row :JOIN@row , 1, 5) = Date@row , 5, IF(INDEX(Item@row :JOIN@row , 1, 6) = Date@row , 6, IF(INDEX(Item@row :JOIN@row , 1, 7) = Date@row , 7, "")))))))

Finds the column index (2–7) corresponding to the trainee who completed training last.

In the Training Record:

Date Position column:

=IFERROR(INDEX({training matrix : Date Position}, MATCH(Item@row , {training matrix : Item}, 0)), "")

Date column:

=IFERROR(INDEX({training matrix : Date}, MATCH(Item@row , {training matrix : Item}, 0)), "")

Name column:

=IFERROR(INDEX({name email column lookup : Name}, MATCH([Date Position]@row , {name email column lookup : Column Position}, 0)), "")

Email column:

=IFERROR(INDEX({name email column lookup : Email}, MATCH([Date Position]@row , {name email column lookup : Column Position}, 0)), "")

Trainee columns:

=IF([Date Position]@row = 2, Email@row )[Trainee 2] =IF([Date Position]@row = 3, Email@row )[Trainee 3] =IF([Date Position]@row = 4, Email@row )[Trainee 4] =IF([Date Position]@row = 5, Email@row )[Trainee 5] =IF([Date Position]@row = 6, Email@row )

These dynamically place the correct email into the proper trainee column based on the position.

Result

This system now allows us to:

• Use a single source of truth (the matrix sheet)
• Extract the right trainee’s name/email via lookup
• Populate structured form fields for export/logging

Paul Newcome

Are you able to provide screenshots that show more data (sample data is fine)?

AJ Barry

https://community.smartsheet.com/discussion/comment/487635#Comment_487635

This is amazing. Is there a way to do it the other way around? eg, the information is added by a form to the training record and the date gets moved into the associated name column and corresponding item row in the training matrix?

jmyzk_cloudsmart_jp

Hi @AJ Barry

Yes, exactly.

https://app.smartsheet.com/b/publish?EQBCT=c32342d819344f799ec0b12364cc8c5e

First, get the [Date Position] by Email from the Name–Email Lookup Sheet.

https://app.smartsheet.com/b/publish?EQBCT=a1a8ad5213734ce588631141af11880e

Next, get the Items using the Row as a key. (In the training record sheet, auto number, in the matrix sheet, text number from 1 to any number necessary.)

Then, use the Item like O0012 to get the Date and its Position.

[Date] =IF(ISBLANK(Item@row ), "", INDEX({training record 2 : Date}, MATCH(Item@row , {training record 2 Range : Item}, 0)))

[Date Position] =IF(ISBLANK(Item@row ), "", INDEX({training record 2 : Date Position}, MATCH(Item@row , {training record 2 Range : Item}, 0)))

Add a column formula to the Name column, for example, Jamie Lee.

[Jamie Lee] =IF([Date Position]@row = 3, Date@row )

Therefore, the "Name Email Column Lookup" sheet is the key to this solution.

https://app.smartsheet.com/b/publish?EQBCT=bb56a91cf86c481eb1c2ff65ce234a29

AJ Barry

https://community.smartsheet.com/discussion/comment/487755#Comment_487755

Is it possible to have 2 formulae in the 'Item' cell in the training matrix? Currently I have separate training matrices for different teams pulling from a 'Master' training matrix. The formulae cross references the information - Formula:

=INDEX({Master Training matrix Range 1}, MATCH(UID@row , {Master Training matrix Range 2}, 0))

AJ Barry

Also if there are multiple trainees trained to the same 'Item' at the same time how does this get captured?

Eg Item OP0012 trained on 24/06/2025 to Trainee 1 and Trainee 2 ( 2 emails listed on one row)

AJ Barry

https://community.smartsheet.com/discussion/comment/487755#Comment_487755

The issue is if more than 1 person is trained to the same item then the dates and date positions get overwritten

jmyzk_cloudsmart_jp

Hi @AJ Barry

In the updated sample, we have multiple emails and dates associated with the same item. (OP0012, 13)

https://app.smartsheet.com/b/publish?EQBCT=c32342d819344f799ec0b12364cc8c5e

I added a new helper column, Rqnkeq Item, and updated the Date and Date Position columns' formula as follows;

[Rankeq Item] =IF(ISBLANK(Item@row ), "", RANKEQ(Row@row , COLLECT(Row:Row, Item:Item, Item@row ), 1))

[Date] =IF(ISBLANK(Item@row ), "", INDEX({training record 2 : Date}, INDEX(COLLECT({training record 2 : Row}, {training record 2 Range : Item}, Item@row ), [Rankeq Item]@row )))

[Date Position] =IF(ISBLANK(Item@row ), "", INDEX({training record 2 : Date Position}, INDEX(COLLECT({training record 2 : Row}, {training record 2 Range : Item}, Item@row ), [Rankeq Item]@row )))

With the 2nd OP0012 and OP0013, we now have two new items in Rows 10 and 11. To treat the first and second items differently, I add the "Rankeq Item" helper columns to indicate whether the item is the first, second, third, and so on.

The updated [Date] and [Date Position] formulas retrieve the first or second corresponding Date or Date Position using the [Rankeq Item] as the index.

https://app.smartsheet.com/b/publish?EQBCT=a1a8ad5213734ce588631141af11880e

Finally, a report grouped by item shows more than one name and date per item.

https://app.smartsheet.com/b/publish?EQBCT=2588a0e723fb4ce99b0fbc746c4de20d

If you do not want to use the report and put the dates in the same row, for example, 06/26/25 in the first row under Juni, you will need separate date and date position helper columns for each concurrent Item assignee. I think it will be possible, but it won't be very easy.