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

Options

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Options

    @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!!!


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    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
    Awade
    edited 10/02/22
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    edited 10/02/22
    Options

    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
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hello @Awade

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