Forms, Formula and fixes - Oh My!

Jayson

Hello,

 

I have created a web based form in which the user fills in certain information. As part of our metrics we need to keep track of when an quote is finished and completed. I have a date column for the quote finish and a checkbox cell for complete status. Our reports use the checkbox cell as the primary filter. 

Currently the user can click the finish box and fail to fill out the finish cell which skews metrics . In order to remedy that I create this formula in the checkbox : =IF(ISBLANK([Quote Finish]1), "Fill Quote Finish").

When the user updates the quote finish cell the checkbox becomes available to click. When the user clicks the check box the formula disappears, which isn't a problem until a new entry is created. 

As expected when a new entry is created the formula isn't there because the cells below it no longer contain the formula. I need to be able to retain this option of forcing the user to enter a finish date before being able to complete it. 

Is there a way to copy the formula into the complete checkbox each time a new entry is created? Will a index and match function work?

 

Thanks in advance,

Jayson

 

 

 

Shaine Greenwood

Hi Jayson,

Since manually checking a box that contains a formula overrides the formula, there isn't a way for the formula to continue to autofill, as the cell above or below it will not have a formula when manually checked.

If people are adding new entries to your sheet via a Smartsheet form, you can make the finish date a mandatory field with the steps in our help center: https://help.smartsheet.com/articles/522221-using-web-forms

 

Jayson

Thank you for the reply. The finish date is unknown at the time of filling out the form. 

 

Thanks again,

Jayson