Gather all matching content (text) from multiple sheets into one cell in another sheet

Options

I am trying to combine text in two different cells in two different sheets into one cell in another sheet.

1) Sheet 1 = Martyn's OKRIs, has two columns 'ID' and 'Martyn's Comments'

2) Sheet 2 = Jane's OKRIs, has two columns 'ID' and 'Jane's Comments'.

3) Destination sheet = OKRI Objective 1 has two columns 'ID' and 'Latest Comment#'

In the cell in column 'Latest Comment#' in sheet OKRI Objective 1, i'd like to collect the text in the cell from the column 'Martyn's Comments' (Sheet - Martyn's OKRIs), only if that row has an 'KR-2' in the ID Column, and then combine this with the text in the cell from the column 'Jane''s Comments' (Sheet = Jane's OKRIs), only if that row has an 'KR-2' in the ID Column....to yield the result as an example: "TQ - On Track. TBC"

What is the correct formula I need to include in the destination sheet that utilises cross sheet referncing?

OKRI Objective 1 (Sheet)

Martyn's OKRIs (Sheet)


Jane's OKRIs (Sheet)


Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!