VLOOKUP Calculation Lag

Stevie Goodwin · 04/11/18

Hi,

I am having problem with a VLOOKUP process.

Our current process flow is as follows

Import data into a "DataDump" sheet through API, at this point each row of data is assigned a number, matching that of the row number (we do this so we can use an index match formula to pull data through to the secondary sheet we call "VLOOKUP"

At this point, the sheet "VLOOKUP" has a column with ascending numbers from 1 to 5000. We then have formula =index(match,match) / VLOOKUP pulling data through to this sheet from the "DataDump" sheet. This then creates a sister copy of raw data.

There are then calculations and check boxes completed to partially analyse this data in "VLOOKUP" sheet

A roll up sheet is then used to summarise this data from the "VLOOKUP" page, before formatted into graphs etc in a dashboard.

Now this sounds like a long process and i am happy to hear views on how we can streamline procedures, but it has been chopped and changed to accommodate a few things

1. Historic raw data can change - we therefore wipe all data from smartsheet and import again (this then takes account of changes to historical data, and updates added data.)

2. If we put calculations into the "DataDump" sheet, they are deleted when API removes all data - therefore we reference the raw data into a VLOOKUP sheet, essentially a secondary data dump.

We are aiming to put a timer on the data entry though the API so that it will update dashboards in real time.

However when we refresh the data into the "DataDump" Sheet we get the error on the "VLOOKUP" page - #NO MATCH

If I click into the cell then press enter, sometimes it will kick into life.

If I leave the sheet unopened however it does not kick into life.

We are aiming for nearly 50 VLOOKUP pages, and would like to be automatic in calculations

Thanks in advance

Steve

Jim Hook · 04/11/18

I have several pages that use either INDEX, LOOKUP or VLOOKUP referencing other sheets without problems but your sheets are so big and have so many complex formulas that Smartsheet may have trouble handling them. A couple of years ago I was having a lot of problems with very large sheets to the point where they would timeout when trying to save them. I ended up breaking the largest sheets into two sheets to cut down on their complexity. I some cases I also made complex sheets run only on the Smartsheet servers, kind of like in the background, so they could use linked-in data, do some complex data analysis on it and then link out to another sheet for display without routinely having to load and save them. Of course, those "background" sheets had to be loaded, edited and saved when I created them but it didn't take a couple of minutes to load/save them daily with the risk of a timeout.

Smartsheet code running in a browser tab is much slower than compiled code running in Excel, for example, and that puts much more restriction on the size and complexity of sheets it can handle.

Stevie Goodwin · 04/16/18

Jim Hook thanks for your reply. It sounds like a similar problem listening to how you describe yours. How do I use smartsheet servers instead of browser memory? Perhaps i can try that and see how it responds?

Thanks

Steve

Jim Hook · 04/16/18

Stevie, Smartsheet servers automatically update links and calculations on other sheets each time you save a sheet that has outgoing links without you having to do anything. For example, if you have a sheet that you make changes on and the new data is linked out to other sheets that use the new data, the Smartsheet servers automatically update the links going into other sheets, runs the calculations on those sheets based on that data and then updates outgoing links to other sheets every time you save the sheet. This takes a few seconds even if there are multiple sheets involved.

So, if you have a large sheet that has thousands of formulas and thus takes a long time to load or save, it is possible to have the Smartsheet servers do that without you having to load it to see the results of what it does. To do that the sheet needs to have all the input data it needs linked into it, it then performs the data analysis needed and then links the results out to either the original sheet or some other sheet. There is a limit of 5000 incoming links per sheet but no limit on outgoing links and up to 500 input links can be created in a single operation. Of course the sheet needs to be initially loaded to be created/edited and saved but after that everything can happen automatically in the servers in less time than it would take to just load the sheet in your browser.

VLOOKUP Calculation Lag

Comments

Help Article Resources

Categories