Copy row of multiple sheets into a target sheet when condition is met

Options

I have 2 sheets with same number of columns (with exception of Function Group name). I would like to create a master sheet also with the exact same identified columns. When a condition is met, i.e, Task is in critical path, then copy the row from the sheet reported that condition into the target sheet. Basically the target sheet is the collection of all tasks that being identified as in critical path. What is the best function to use to achieve this? Please advise! Thank you!

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @Huong

    I hope you're well and safe!

    • You could use a Copy Row Workflow.
    • You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.
    • Another option would be to use so-called helper sheets. In short, copy the row to a helper sheet and then use my method described previously to get the values you need to another helper sheet, and then copy/move the row from that sheet to the main destination sheet.

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • Huong
    Huong ✭✭
    Options

    Hi @Paul Newcome,

    The team was thinking of using a "master" sheet to collect the status from different sheet and work off of that, so we didn't think of creating a report. Please advise on how to generate a report that would collect all "critical path" items from various sheet and combine into one. Thank you!

  • Huong
    Huong ✭✭
    Options

    Hi @Andrée Starå,

    I was successfully created multiple sheets and have the "critical path" tasks "copied" into a master sheet to report all critical path tasks.


    My trigger is: When rows and added or changed AND when 'Critical Task (Y/N)' changes to 'Yes"

    Action: Copy a Row


    However, there're duplicates if any of the copied row (tasks) with "critical path" condition were changed/updated. Is there a way to copy the "critical path" tasks from other sheet into the master sheet -- to the same row if one already exists -- so without creating duplicated rows. Please advise! Thank you!