Background sheet not updating

Brad Kuskye

I have a rather complex process I'm building out in Smartsheet, and I've run into an issue with one of my background/calculation sheets not updating and triggering my workflow.

I have a sheet (FORM RESPONSES in the linked workspace) with a form attached that collects a bunch of information - one of those pieces of information is a list of names in a freeform text entry, as the number of names collected is variable. After the form has been submitted I have a background/calculation sheet (BACKGROUND/SPLIT SHEET in the linked workspace) that uses VLOOKUP to pull the info from the submission row.

This sheet breaks apart the list of names into individual rows based on this solution by @Paul Newcome. I then have a checkbox column (Finished) with a formula that checks the box if the row number is less than the number of items in the original list of names.

Next, I have a workflow set up to look for rows being added/changed and copies each row with a checked box in the Finished column to the final sheet in the process (FINAL SHEET in the linked workspace). This workflow is not triggering unless I open the sheet - which causes the "Save" button to immediately un-grey and become clickable, indicating those changes were not made until I opened the sheet.

I need this process to happen in the background without having to open the sheet. I'm thinking it's due to the VLOOKUP not actually happening unless that sheet is open, but I'm not sure if that's the case. If it is, is there a better way to do this?

I have created a copy of the sheets involved in this process in a folder you can request access to here.

Thanks in advance!

Brad Kuskye

Well, over the course of writing this question I discovered the answer. VLOOKUP does indeed update behind the scenes, but it is slow compared to when you have the sheet open. I'm not sure what the exact time for updates is - longer than five minutes at least, but it does eventually propagate the changes and trigger the workflow.

Consider this solved.

Brad Kuskye

Well, over the course of writing this question I discovered the answer. VLOOKUP does indeed update behind the scenes, but it is slow compared to when you have the sheet open. I'm not sure what the exact time for updates is - longer than five minutes at least, but it does eventually propagate the changes and trigger the workflow.

Consider this solved.

Paul Newcome

Try replacing your VLOOKUP formulas with INDEX/MATCH. INDEX/MATCH is much more efficient and should help improve back-end speed.