Saving Time Loading and Saving Large Smartsheets with Many Complex Formulas

Jim Hook

I have several Smartsheets that have had to be broken up into two sheets because of the sheer size of the sheet and the number of complex formulas that used nested IF() and SUMIFS() functions (>1500 cells with complex formulas). These sheets would take a long time to load and even longer to save, sometimes timing out, after editing. In several cases I've been able to move the complex parts of these sheets to what I consider to be "background" processes that run entirely on Smartsheet servers rather than having to be loaded and run on my Smartsheet browser tab.

These background sheets work by having data that needs to be processed linked in from some other sheet, or via the API in some cases, processed extensively using complex formulas and then linked out to other sheets that use or display the processed data. During normal daily operations I never have to open these complex sheets. In addition to processing very complex sheets I have also generated some specialized sheets that can alphabetize or sort in numeric sequence data from some source sheet before it goes to a sheet used mainly for displaying results from multiple places. Since Smartsheet doesn't support macros or automatic sort functions there was no other way to do this. I often use Smartsheet reports for viewing data but it's not possible to use the output of a report in another sheet.

Since the extensive processing and linking is done on the Smartsheet servers it saves a lot of time loading and saving large sheets on my browser. The only downside is that when new data is introduced into my system it can take up to 30 seconds for the data to link and be processed by the servers and ultimately get to my display sheet. I'm fine with that since it all happens automatically.

 

Gwyneth C

Hi Jim,

Thanks for sharing this solution. Is there a request or a part of this that you'd like others to weigh in on?

Brandy S. PVD

How did you accomplish having these sheets work directly through the Smartsheet server rather than your web browser?

Thanks,

Brandy

 

Jim Hook

Hi Brandy,

I had been doing some of this for a long time without realizing it until I needed a Smartsheet that would take a couple hundred timekeeping records and compress them to reduce the total number of records going into my Excel timekeeping database. The compression Smartsheet was so large that it wouldn't actually load in Chrome, giving up after a couple of minutes. Fortunately it would load in Firefox so that's where I finished developing it. Here's how it works without ever being loaded in my browser.

1. Link all the data into the sheet from the main data source. In this case I was linking in 700 cells with data in them. When new data comes in the Smartsheet server automatically updates the input links, performs the compression using the algorithm in the sheet and populates the output data area for linking out all in what I call the "background" without ever going through my browser. Smartsheet always automatically updates links and calculations when one sheet changes that has links to others and I make a lot of use of this capability.

2. Link all the compressed records out to another Smartsheet that simply displays the compressed records. This sheet has just a couple of calculations and no conditional formatting so it loads very quickly, just a few seconds.

The fact that data is linked into and then out of the large sheet automatically by the server means that I never have to load the large sheet in my browser unless I need to change the linking or algorithm which I haven't done since I got it working three months ago. My project timekeeping system now has more of this type of sheet than the normal type since I use this method for validating data inputs, generating a list of the most active projects on a weekly basis, sorting lists of things in alphabetical or numeric sequence automatically, etc. Large Smartsheets with a lot of complex formulas take a long time to load and putting them in the background using input/output links eliminates the need to load them.