Formula to auto-transfer data into another sheet

09/25/21
Answered - Pending Review

The company I work for uses Smartsheets to build schedules. We have both weekly and daily schedules. However, I've noticed a lot of our time is spent daily going through and transferring information from the weekly to the daily grids.

I didn't know if there was a way for me to auto-link certain grids to a grid on another sheet so that, for example, when I enter data for manager on the Monday slot for the weekly grid, it will automatically put that data in the manager slot on Monday's separate daily grid.

It appears this may be able to be done via conditional formatting, but the exact way I would do so eludes me. Any help is appreciated.


Thank you.

Answers

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

    Hi @Comthomp

    I hope you're well and safe!

    You could probably 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.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    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 PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Andrée,


    Thanks for your response! I think you've understood my question. I suppose to explain it further, it's just as you say. The weekly schedule would be the source sheet while the daily schedule would be the destination sheet.


    Basically I am seeking to avoid having to enter the same data multiple times across multiple sheets. I have included two of our older smart sheets below. The first is our full week schedule back from July 19 to July 25. I then have also included the daily schedule for Tuesday, July 20.


    I understand we'd maybe need to change the format a bit, but I would want some of the cells in the source sheet to populate another field automatically, depending on content, in the destination sheet, therefore not requiring us to enter the same information twice in two sheets.


    The weekly schedule — (July 19-25) https://app.smartsheet.com/sheets/PvrCHHRwPJ5cmVQV8q6H98GPp9JgxR7QW29xMPg1

    The daily schedule (Tuesday, July 20)

    https://app.smartsheet.com/sheets/mc74MqPPgW5pH7rqWg9p8VQrWV53mjMWJqg3gRp1

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

    @Comthomp

    Happy to help!

    I've requested access to the sheets.

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

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

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

Sign In or Register to comment.