Very Large Save Causes Formula Corruption

I've been trying to understand a problem I'm having with two of my largest Smartsheets. If I move a row up or down or insert or delete a row, especially near the top of the sheet, the Save process will apparently corrupt formulas here and there in my sheet with no warning or error message given. I've gone back and forth with Smartsheet support several times and they say this is caused by having a large number of rows and columns, the number and complexity of formulas, amount of conditional formatting and the latency from my computer to the Smartsheet servers. No specifics can be given as to sheet size or number of formulas is available to let you know you are getting close to having a problem so I'm going to have to break my sheets up into two or more smaller sheets to solve the problem which will be inconvenient.

The sheet I have the most problems with is quite large with 1600 rows, 31 columns, three conditional formatting rules and about 20,000 formulas ranging from very simple to complex nested IF statements. It also has about 1600 incoming links. It takes a couple of seconds to save if no rows have been moved but 30 - 120 seconds if rows are moved, especially near the top of the sheet.

Does anyone know of any guidelines on sheet size or number of formulas that would be "safe"? I fixed all the formulas yesterday and then inserted about 12 new rows about 20% from the bottom of the sheet for readability, saving after every three or four row inserts, and that corrupted about 250 total formulas in about 30 rows at random places in the sheet.

Find more posts tagged with

Finance

Formulas

Reports

Integrations

Conditional Formatting

Comments

Jim Hook

I broke my two largest sheets into two independent sheets yesterday and will test to see if they are now small enough to be stable next week. I also created a new sheet that compares total hours on each of our active projects between these two sheets that should detect if any formulas get corrupted. Frankly, I can't believe that this is a problem with Smartsheet. This is unacceptable. Ultimately, if you can't trust the calculations you can't use the product.

Travis

Hi Jim,

Thanks for posting! It sounds like you have a lot of factors in your sheet that increases the complexity.

In your example, you have 20,000+ formulas, formatting rules, cell links, etc. When you enter a row in your sheet- say at the top of the sheet - every row under needs to be updated. That means 20,000 formulas are changed to reference the new rows, 1600 cell links are updated, 49,600 (1600 x 31) cells are updated etc.

Because of the amount of data changing, it might not happen instantly. It could take a few seconds for everything to update but everything should update correctly.

We have seen users insert/delete rows then immediately save. The save is initiated before all the data/formulas can update and causes some formulas to reference incorrect rows.

Try this, when adding/deleting rows in your sheet, wait 10-30 seconds or so before saving the sheet to give the data time to update.

Jim Hook

Travis, thanks for your suggestions. I don't understandy why the save function in Smartsheet doesn't automatically wait until the cell references and links have been updated. I spent two days splitting my biggest two sheets into two smaller ones and then half a day testing to see if that fixed the problem. At this point it seems to be working. I noticed that the save icon goes dim for a few seconds after a major row relocation near the top of the sheet the first time I do it. The next time, before the save, it only dims very briefly. Is that the formula engine saying it is busy? If so, why would it only dim for a fraction of a second the second time but several seconds the first time?

Travis

I agree the process could be better. I spoke with our VP of Product today and he told me a little bit about our upcoming formula enhancements. Apart from new functions, the new formula engine will calculate and update much quicker so these sort of issues shouldn’t be a problem anymore.

He was not able to give me a timeline but assured me they are working hard to get it released.

Jim Hook

That's good to hear. Performance increases will be great but they must not happen at the cost of accuracy. In other words, on my large sheets I would rather wait another minute for a save than have it corrupt formulas.

J. Craig Williams

Amen to "rather wait another minute for a save than have it corrupt formulas"

I remember having to go home and get some sleep while the computer recompiled my program. Now we complain about seconds. I'm as guilty as the next, but sometimes ... I forget.

No, wait. That was my father. I'm not that old.

Craig

Jim Hook

Here's an update on my formula corrupton problem.

I made a copy of my biggest problem sheet filled with dummy data and shared it with Zack at Smartsheet. It was so big and complex that you couldn't move a single row near the top of the sheet and still be able to save it, it timed out every time with an error mesage to check my internet connection. After paring it down by 20%, I tried everything I could to get it to corrupt formulas including inserting multiple rows near the top of the sheet one after another and then saving it as soon as it let me. I could not get any formulas to be corrupted. If I move a few hundred rows from the bottom to the top of the sheet I would still get a timeout and the internet message though. On the screen it looked like the move had been done but I couldn't save it. Refreshing without Save would restore the sheet but I would lose all my edits since the last save. So, I couldn't find a reason to wait after moving a row to let the formula engine complete. Everything works fine as long as you can save the sheet without timing out.

I believe the root cause of my problem was self-induced merge faults caused by my having two separate browser tabs open in Chrome each with the large sheet open, making separate changes to each and then saving both of them within a few seconds. Apparently saving in this way causes the two instances of the sheet to be merged incorrectly. While this is a concern, I now know how to avoid the problem.

In the end I rearchitected both of my problem sheets to drastically reduce the number of formulas and everything is working fine now, and much faster.

J. Craig Williams

Jim,

That's good to hear.

I don't have to deal with so much data (yet) but nice to know there is a semi-solution.

Craig

Roland Horwood

Hi,

a bit after the fact, but I've been having similar problems with corrupted cells (formulas misaligned, referencing the wrong row after a nearby row is moved).

The advice I got from support is:

"First, since you're working with a very large sheet, moving a row to different location will cause all of the formulas in surrounding rows to update. Depending on how many rows have formulas in them, this could take a minute or two.

Second, if there are multiple people working on the sheet at one time, this updating process could be interrupted if another user makes a change in the sheet. If a user is going to move around rows, I suggest blocking off a time to warn other users to not use the sheet while that is happening.

The important thing to wait for, when updating a large sheet, is the save button. When the save button lights up, after the row move, it means the formulas have all updated. Once they've updated, I suggest saving the sheet before other users interact with the sheet. Smartsheet also has an 8mb maximum size limit per save. With this limit, I suggest saving frequently.

In summary, I suggest waiting for a time where only one person is viewing the sheet to move rows around. Also, let the sheet update (wait until the save icon lights up again) before making any other changes to the sheet."

Not ideal, but we're trying this approach to see if it mitigates the problems. Hopefully I won't have to waste more hours correcting corrupted formulas!

Roland

Roland Horwood

Just to keep this updated...

My users are more disciplined now on saving after inserting/moving rows. We ensure there is only one person viewing the sheet, and that they don't save until everything is updated OK.

This seems to have mitigated the worst of the problems - I'm not repairing the sheet every week..

...However we still get the issue approx once a month, which is still a pain in the A. Surely a lock of some sort can be applied whilst formulas are rippling through the sheet? If not automatically, at least by a user.

I would much rather SmartSheet dedicated their resources to improving the performance, reliability and functionality of the product rather than mucking around with the User interface.

It's almost a brilliant product, it just doesn't cope well with large'ish sheets.

Roland

Jim Hook

I haven't had problems like this for a long time now since I split-up my largest sheets into two smaller sheets. We also have fewer users with edit ability on the same sheet to avoid the problems caused by two or more people editing the same sheet at the same time. I also carefully watch for the save icon to go dim after loading a sheet before doing anything.

Some of the newer functions have helped reduce the size of some sheets also. I'm talking about the LARGE and SMALL functions that have made sorting things in order much easier than using massive "bubble sorts" that I had in the past.

Basically, as you said, Smartsheet doesn't do well on very large sheets so I keep them smaller than in the past. I guess that's a symptom of Smartsheet being browser-based instead of a standalone app that runs on a compiled language like Excel. I still like Smartsheet for its many nice features but know how to work around its limitations.

Roland Horwood

This is still causing us horrendous problems. After several months of it being OK we've had 4 major corruptions in the last couple of weeks. Our sheets are hard to split into smaller ones without a lot more cross sheet links, which would probably make the situation worse. I've now removed 30-odd columns that were all calculations (forecasting) to see if that helps. We've also been training our users in very specific update processes (unfortunately the sheet corrupted whilst I was training them, embarrassing).

It wouldn't be so bad if there was a roll-back feature, or at least the ability to highlight cells with calculation in them (we overwrite calculated dates in cells with actuals).

No matter my interactions with the support team, who are helpful, and try to explain why things go wrong, I still can't understand WHY there aren't safeguards in the system to STOP them gong wrong. Surely there must be some way of locking the system to stop overwriting of changes half way through a calculation cycle?

Frustrated of Herefordshire.

Jim Hook

Hi Roland, did you notice any worsening of you sheet corruption problems after the recent Smartsheet major user experience update? I've noticed that the save icon doesn't go dim until the sheet completes recalculating immediately after reloading but I can tell there's still something going on behind the scenes since some actions seem to be delayed for a few seconds. On very large sheets this could be an extended period of time during which bad things could happen if you are making changes. You might try getting access to a specialist at Smartsheet that has more capability, and access to their development team, to better understand how things work so you can devise ways to work around your corruption problems.

You mention that people are overwriting cells with date functions with actual date entries. You may be able to help in that area by locking columns and/or rows with calculations so that they can't be edited by others. That may take some reorganization of some sheets but is critical if many users have access to all cells including those with formulas.

As I mentioned back in March my problems have gone away after breaking my sheets into two parts sometimes and using some of the newer functions to drastically reduce the size of some sheets. I experimented with exporting sheets to Excel to make backups of critical sheets. That preserves the formulas in cells but loses all the links, not a real help.

I agree with you that Smartsheet needs a way to roll back to earlier versions of sheets. I use Dropbox for cloud storage and they make the last 100 saved versions of documents available for reverting back to earlier versions. That dramatically increases the amount of storage used but it has saved me a couple of times.