Keeping Reference Sheet Formula

firestorm

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.

MCorbin

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

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å

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!