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

tina.hutchinson52776

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)

Paul Newcome

You would use an INDEX/MATCH to pull over the text from one sheet, replicate that for the other sheet, then "add" the two together.

=IFERROR(INDEX({1st Sheet Comments}, MATCH(ID@row, {1st Sheet ID}, 0)), "") + "delimiter of choice" + IFERROR(INDEX({2nd Sheet Comments}, MATCH(ID@row, {2nd Sheet ID}, 0)), "")

tina.hutchinson52776

@Paul Newcome How can you modify the formula to take into consideration when a cell in one sheet (Sheet 1) i'm pulling the text from is blank but the cell in the other sheet (sheet 2) i'm pulling text from is not blank. I have used your formula, sheet 1 cell is blank but sheet 2 cell has text, but it's not pulling any data from either sheet 1 or sheet 2 into my destination sheet / cell.

Paul Newcome

It sounds like there may be an error somewhere. Lets remove the IFERROR statements and see what we get...

=INDEX({1st Sheet Comments}, MATCH(ID@row, {1st Sheet ID}, 0)) + "delimiter of choice" + INDEX({2nd Sheet Comments}, MATCH(ID@row, {2nd Sheet ID}, 0))

DB393

Hey @Paul Newcome

I tried using your formula for combining (2) texts from separate sheets and got an unparseable error. Here is my formula for context.

=INDEX({msifn}, MATCH(Prospect-ID@row, {msifd}, 0)) +"--"+ INDEX({ssifn}, MATCH(Prospect-ID@row, {ssitfd}, 0))

Also does smartsheet have the capabilities to have a carriage return as a delimiter? Ideally I would like to replace the "--" delimiter with "CR-" (CR= Carriage Return).

Thank you for your assistance!

Paul Newcome

@DB393 Any column name that has spaces, numbers, and/or special characters (including hyphens) need to be wrapped in [Square-Brackets] when using them in a formula.

The carriage return delimiter is CHAR(10).

DB393

Updated formula:

=INDEX({msifn}, MATCH([Prospect-ID]@row, {msifd}, 0)) +char(10)+"-"+ INDEX({ssifn}, MATCH([Prospect-ID]@row, {ssitfd}, 0))

I am getting an invalid operation error.

For reference I did double check to ensure the column were all the same type of data.

I also tried this formula as a test and was able to get the first sheet's text to pull however the second sheet did not show:

=JOIN(DISTINCT(COLLECT({msifn}, {msifd}, [Prospect-ID]@row)), CHAR(10)) + JOIN(DISTINCT(COLLECT({ssifn}, {ssitfd}, [Prospect-ID]@row)), CHAR(10))

Paul Newcome

Do any of the columns being referenced have that error in them?

DB393

I was able to find the problem.

Here is the solution for reference:

="-" + INDEX({msifn}, MATCH([Prospect-ID]@row, {msifd}, 1)) + CHAR(10) + "-" + INDEX({ssifn}, MATCH([Prospect-ID]@row, {ssitfd}, 1))

Result:

Thank you again for your assistance!

Paul Newcome

What was the issue?