Row Linking Live Data onto a New Sheet from Multiple Sources

Options

I am looking to have a rows automatically transfer from Sheet1 and Sheet2 to Sheet3 when certain criteria are met, ie: the row is assigned to a specific person. The goal is for Sheet3 to be a summarized page of all the rows assigned to this person from multiple different sheets. Sheet3 would have live data from Sheet1 and all the other sheets that we are feeding into it.

I had originally thought about doing a report -- which would keep the data live -- but I believe reports won't let you add new columns for unique data entry, it only will take columns from the other sheets. I need to add a new unique columns to Sheet3 for the assigned individual to add data.

I have been using to the "copy row to a new sheet" automation, but it doesn't update the row on Sheet3 when the fields in Sheet1 are updated, the best I can get is for it to copy over the updates in a new row, which becomes duplicative and messy. Is there a way for that automation to just update the same row instead of copying a new one?

If there is any workaround for this please let me know.

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Options

    Hi @crodts

    Unfortunately without manually cell linking between the two rows (i.e - sheet 1 - sheet 3) you cant update a copied row automatically.

    As reports wont work due to the column issue there is possibly one other thing you can do.

    I have a similar issue where i need to keep the latest version of information across multiple rows that are being copied over from helper sheets.

    Do the rows have a unique identifier other than the persons name, i.e. a project name or similar? Do the feeder sheets have a system column date created, or could that be added to the feeder sheets?

    If yes then you could let the sheet copy over updates in a new row then use a MIN formula to update a helper column looking at that unique identifier and the system date created column, the MIN formula could then identify the earlier update by using that unique ID and date.

    You can then use a workflow to move (delete) the row to another sheet, you can then periodically clear that sheet or I find keeping that info to refer back to if needed quite helpful.

    Hope that helps

    Thanks

    Paul

  • crodts
    crodts ✭✭
    edited 01/17/23
    Options

    Hey Paul, that might work, could you show me what you were thinking in terms of MIN formula and how that would be applied to the sheet?