How can I collect multiple values from other source sheet and show values in one cell for my master?

Jamil James

Kelly Moore

Hello @Jamil James

It sounds like the JOIN(COLLECT) combination is what you need. The collect gathers a data based upon specific critera. The Join will concatenate all the data into one cell, separated by a character or 'delimiter'. The formula will have the syntax of:

=JOIN(COLLECT({source sheet column where data you want resides}, {source sheet range 1}, criteria 1, {source sheet range 2 (if any)}, {criteria 2 (if any)},etc), delimiter)

If you can show us your source sheet and target sheet, we can create the specific formula for you.

Kelly

Jamil James

@Kelly Moore

Thank you for the quick response. To provide more context on this issue.

I have one main source sheet and two separate sheets labeled Snip, and SCP. If a row is added to Snip sheet i would like for it to land in the main source sheet column Row ID (Test), if a row is added to SCP sheet i would like for it to land in the same main source sheet column Row ID (Test) if neither happens, I would like for it to say No ID.

The data will need to be in a column labeled Row ID (Test) Main Source Sheet. Thank you so much!!!

Source main sheet.png

SNIP.png

SCP.png

Kelly Moore

Hey @Jamil James

I may have misunderstood your original request. Are you asking to push entire rows from one sheet to other sheets (this can be done) or, populate a single column with new row data based on data in a different sheet. It sounds like the latter.

Assuming it is the latter, if you have some existing information in the rows of the other sheets that can be used as a reference to your source, this can be done. If are trying to keep sheets synchronized with one another without a reference point- that's a different question.

Awade

Hello Kelly,

I have a similar question for which I think your explanation of: =JOIN(COLLECT({source sheet column where data you want resides}, {source sheet range 1}, criteria 1, {source sheet range 2 (if any)}, {criteria 2 (if any)},etc), delimiter) works.

However I have one target sheet and multiple source sheets. I want to collect a date range on 4 or 5 source sheets, tie the date range to data in a different column, and transfer the aggregate of all source sheets in that date range to a single sheet. Is there a way to expand on the Join/Collect formula above to do this? Many thanks.

Kelly Moore

Hey @Awade

You won't be able to pull from multiple sheets. However, you may be able to concatenate multiple Join/Collects together. If you want to give me screenshots (remove sensitive data) of your sheets I will try to test it what I mean. I

In my mind, I'm thinking the formula below (boldface), where each Join/Collect is from a different source sheet. The idea is modeled after basic concatenation (joining) of cells (if we were working with cells). In the example I added a delimiter so that the spaces I bracketed around the delimiter were more visible. In your case you may only have a space following your delimiter. See my bold formula below the screenshot example.

=Join(Collect)#1+"whatever delimiter you are using, don't forget to add the space, all within these quotes"+Join/Collect#2+"whatever delimiter you are using, don't forget to add the space, all within these quotes"+Join/Collect#3+"whatever delimiter you are using, don't forget to add the space, all within these quotes"+Join/Collect#4

Try this and let me know if it works. Or share the screenshots and I'll try to work it out. Be sure to include target and source sheets.

Kelly

Kelly Moore

Hello @Awade

Did my previous reply work for you? Are you still having trouble?