How do you force automation to wait until row is finished calculating before copying over?
I have an automation set up to copy rows to a new sheet when a row is added or changed. I am not able to set it up for a certain time of the day because the sheet may be updated more than once in the same day and some of the data is deleted. I need all of the data input at any time on the other sheet. When I set up the automation it copied the rows over however on some of the formulas on the sheet it put #Calculating instead of the value I needed.
Best Answers
-
Sounds like you have a lot of formulas in your sheet, and if there are a lot of simultaneous users, this can certainly slow down the formula calculations.
Here's an idea - Use a helper column that counts the number of values in your formula fields, and use that as the trigger to copy your row. For example, say your formulas are in one date field, one number field, and one text field, you could create a formula that adds up the cells that have the correct data format, and when it equals 3, that triggers the automation. =IF(ISDATE(StartDate@row), 1) + IF(ISNUMBER(Total@row), 1) + IF(ISTEXT(name@row), 1)
This way, if any of the three fields are calculating, and therefore not the format they're supposed to be, the helper cell does not equal 3, and the row is not copied until the cell DOES equal 3.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
This worked perfectly thank you for your help
Answers
-
Is there some point where the people entering/changing/deleting data know that the row is "done" and ready to go? You could add a manual checkbox that is the trigger to tell automation to execute the row copy.
Only other thing I could suggest is the Data Mesh premium add on that will create new rows in the other sheet and update the data to match the source sheet. (Data Mesh will NOT delete rows from the destination if they are deleted from the source though.)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
I don't want to rely on a person to do it before deleting a row. There are too many people working in the sheet. If it isn't copied over then the data is lost and can't be recovered. Also I don't want the destination sheet to necessarily match the current data on the source sheet that is why anytime a change occurs it copies it over. I just want the formulas to finish calculating and have the actual data at the time of the change instead of inputting the #calculating
-
Sounds like you have a lot of formulas in your sheet, and if there are a lot of simultaneous users, this can certainly slow down the formula calculations.
Here's an idea - Use a helper column that counts the number of values in your formula fields, and use that as the trigger to copy your row. For example, say your formulas are in one date field, one number field, and one text field, you could create a formula that adds up the cells that have the correct data format, and when it equals 3, that triggers the automation. =IF(ISDATE(StartDate@row), 1) + IF(ISNUMBER(Total@row), 1) + IF(ISTEXT(name@row), 1)
This way, if any of the three fields are calculating, and therefore not the format they're supposed to be, the helper cell does not equal 3, and the row is not copied until the cell DOES equal 3.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you I will try that
-
This worked perfectly thank you for your help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!