Help with cross sheet references.

Dave Moe

Good morning,

I am looking to pull the data in a cell if two other criteria are met in other cells in the same row. for example, I have a sheet called Daily reporting that is receiving form submissions. I want to pull the text in [Quality-Details] if [Column17] is equal to today AND [Value Stream Leader] is equal to Sergio C. into a new sheet. I have tried Index/match and collect but cannot seem to get anything to work. Any guidance would be appreciated.

2020-03-27_12-10-24.jpg

Genevieve P.

Hi Dave,

You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell's value, assuming that only one row meets all the criteria).

The way JOIN(COLLECT works is that you first list the range that has the value you want returned from your fixed data sheet, and then list each range and criteria in the current, second sheet afterwards.

For example:

=JOIN(COLLECT({Sheet 1 - Quality Details column}, {Sheet 1 Column17}, =TODAY(), {Sheet 1- Value Stream Leader}, "Sergio C."))

The ranges in {these} are cross-sheet references to the columns in your first sheet. If Sergio has more than one row where the date in Column17 is TODAY then all of those Quality Details will be returned in the same cell.

You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / Cross Sheet References

Let me know if this works for you!

Cheers,

Genevieve

Andrée Starå

@Dave Moe

Hi Dave,

To get the result on separate lines you can use the CHAR function.

CHAR(10) for line breaks

More info:

https://help.smartsheet.com/function/char

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Genevieve P.

Hi Dave,

You could use a JOIN(COLLECT formula for this, even though you don't have values to Join (you'll just be returning one cell's value, assuming that only one row meets all the criteria).

The way JOIN(COLLECT works is that you first list the range that has the value you want returned from your fixed data sheet, and then list each range and criteria in the current, second sheet afterwards.

For example:

=JOIN(COLLECT({Sheet 1 - Quality Details column}, {Sheet 1 Column17}, =TODAY(), {Sheet 1- Value Stream Leader}, "Sergio C."))

The ranges in {these} are cross-sheet references to the columns in your first sheet. If Sergio has more than one row where the date in Column17 is TODAY then all of those Quality Details will be returned in the same cell.

You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / Cross Sheet References

Let me know if this works for you!

Cheers,

Genevieve

Dave Moe

Thank you so much Genevieve, I came across the Join/Collect on Friday and was able to make it work. The one aspects I am missing is that I am trying to combine multiline information from cells, is there a way to make where they are joined on separate lines instead of using a delimiter like "-"?

Andrée Starå

@Dave Moe

Hi Dave,

To get the result on separate lines you can use the CHAR function.

CHAR(10) for line breaks

More info:

https://help.smartsheet.com/function/char

Would that work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!