Keeping Reference Sheet Formula

Options

Hello,

In Smartsheet, I have a metrics file that has formulas to consolidate numbers (sumifs) by referencing a raw data file. The first version of the raw data file was imported from an Excel file, and it has data in thousands of rows. Every 2 weeks, the Excel raw data file will be generated by our system and I will get an email copy.

Problems:

  1. I have to delete the old data in the raw data file in Smartshee before copying the new data in. I tried to delete all the rows but realized I can't save the file if the changes are too big. Hence, I had to delete by batches.
  2. Similarly, I have to copy the new data into the Smartsheet in batches, as there is a limit of 500 rows.

Is there a way to import the fortnightly data file and keep the reference formulas in my metrics file? Or is there other better method?

Thank you.

Answers

  • MCorbin
    MCorbin Overachievers Alumni
    Options

    If you don't already own Data Uploader, I'd highly recommend it. I have several solutions similar to this and Data Uploader rocks my world for these. It's a premium app, but not super costly, and it definitely is worth it!

    In terms of your reference formulas.... Create a "Dummy" first row that isn't real data, but has all your formulas in it. Don't delete it when you delete the other rows. When you add your new rows in, your formulas should copy down to the new rows.

  • firestorm
    firestorm ✭✭✭
    Options

    Yes, I would keep a couple of rows just to copy some of the extra columns which I have created to calculate some data. OK, so it means there is no other option but delete few hundred rows at one time, save the file and continue till all rows are deleted, and copy 500 rows of new data by batches. :(

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

    Hi @firestorm

    I recently developed a solution for a client where they paste the information in a specific pattern from an exported Excel file from their ERP system to Smartsheet. Then tasks and material are added to the Project Plan and more automatically.

    Would something like 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 help the Community by marking it as the accepted answer/helpful. 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.