Moving DATA from source sheet into new sheet that has formulas that need to be applied to new data

Options
Jeana
Jeana ✭✭✭✭✭✭
edited 09/13/21 in Formulas and Functions

Hi, I have developed a new sheet (NEW PROJECT SCHEDULE) for our PM's to use that has a lot of formulas in it, on almost every row (Parent and Child rows) as well as Summary formulas for a Dashboard.

This prototype has tested and worked well for new Projects because the original data is being copied to the NEW PROJECT SCHEDULE through automations from other sheets and formulas.

The problem is this. The PM's need to be able to transfer current Project Sheets to the NEW PROJECT SCHEDULE. These projects are in process and it wouldn't work have them enter data from scratch. It also doesn't work if they try to copy/paste rows or even cells of data into the NEW PROJECT SCHEDULE because it writes over the formulas.

I can have them copy paste the data and then manually go back and enter all the formulas (just did that for one sheet and it took me about 3 hours).

I don't have Control Center. Is there another method anyone can recommend to get the existing Project data from one sheet to another and keep formulas in tack in the new sheet?

Jeana

Tags:

Best Answer

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

    Hi @Jeana

    I hope you're well and safe!

    If you convert everything to Column Formulas, it should work. It can be a lot of work, though, because you have to factor in all possible options to make it work.

    Make sense?

    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.

Answers

  • Jason Duryea
    Jason Duryea ✭✭✭✭✭✭
    Options

    Are you able to utilize column formulas, or does the design require cell formulas for it work?

    I’ve found that if cells or columns are formula driven, you wouldn’t want to copy & paste the cells, but only copy the cells that are data only and let the fat drive the formula results for the rest of the schedule. This may mean copying one or two columns at a time and ensuring the rows align when pasting into the destination sheet. Copy & pasting can be tricky in Smartsheet, but there is no other way that I’m aware of do this type of data migration. The import feature creates an entirely new sheet, rather than importing into an existing sheet.

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

    Hi @Jeana

    I hope you're well and safe!

    If you convert everything to Column Formulas, it should work. It can be a lot of work, though, because you have to factor in all possible options to make it work.

    Make sense?

    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.

  • Jeana
    Jeana ✭✭✭✭✭✭
    Options

    Thanks, converting to Column formulas might help. I will still have to go back and change SOME formulas at different levels because they aren't all literally Column formulas.

    I'll give it a shot.

    Have a good one!

    Jeana

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

    @Jeana

    Excellent!

    You're more than welcome!

    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!