Is there a simple, elegant way to copy rows with linked cells to another sheet?

Options

In my specific case, I have a summary sheet where each project has one row with data linking from a detailed project plan sheet (e.g. - Dates, Budget Hours, % Complete, etc...). I want to copy rows to another sheet where I will be creating Card views for specific sub teams. I want the data to be dynamic as changed only in the detailed project plan sheet. Using a simple automation and copying rows as they are added to the summary sheet does not maintain linked cell connections to the detailed project plans - just static data. Is there an elegant solution to this dilemma? Thanks!

Answers

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

    Hi @Ernie Levenson

    I hope you're well and safe!

    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.

    Would that work/help?

    I hope that helps!

    Have a fantastic weekend & Happy Holidays!

    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.

  • Ernie Levenson
    Options

    Andree,

    Thank you so much for quickly answering the question. I'll have to play around with the INDEX/MATCH functions. If you have specific examples that are similar to what I am trying to accomplish - that would be great. If not, no worries - I'll keep dabbling around. I would hope the SmartSheets folks and internal developers will consider some of these potential enhancements (e.g. - carrying over linked-in cell info and other dynamic types of data - when copying a row from one sheet to another).

    Have a great holiday season as well!


    All the best,

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

    @Ernie Levenson

    Excellent!

    You're more than welcome!

    Try something like this.

     =
    INDEX({ColumnWithTheValueYouWantToShow}; 
    MATCH(CellThatHaveTheValueToMatch@row;
    {ColumnWithTheValueToMatchAgainsTheCell}; 0))
    

    Depending on your country/region, you'll need to exchange the comma to a period and the semi-colon to a comma.

    Remember! 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!