Permit formulas in columns with the "Restrict" value toggle enabled

ritzbitz00

I have columns that are set to take certain types of data, and they contain formulas that fill the cells with values permissible within that data type. For instance, there's a field where a formula fills in one of several options based on a cell reference, or the user can manually select one of the options from a dropdown menu. I have another field where cells reference another to return dates, but a user can overwrite that date with a different one if necessary.

If i turn on the toggle to Restrict values to the field type, I get error messages that there are values in the cells that aren't permitted: formulas in cells, even when they return the appropriate data type, are not considered a value match for the column and are removed. This is prevents the use of data validation and value restricting in fields that contain formulas, and introduces data hygiene issues that should be resolved

I would like for this to be fixed. Excel and other spreadsheet software are able to recognize formula results as a value type, but Smartsheet blocks formulas from working in fiels with value-type restrictions.

Genevieve P.

Hi @ritzbitz00

I'd like to clarify that if you're an Admin or Owner of the sheet you can have values that go against the restricted type 🙂

This means that you, as the Admin, can add in formulas to the cell and then restrict the column (or do so after).

You'll still see the error message but it won't remove the formula, it's just letting you know that one of the cells contains something that goes against the restrictions (so you're aware).

Then you can click "Allow" and that formula will stay in the cell. Does that make sense?

ritzbitz00

Hi @Genevieve P.

Thank you for your response. I am an Admin and Owner of these documents, but am not the primary user of the sheets. Our regular users are the ones doing most of the editing, and they need to be able to make new rows to do their work in. these new rows inserted in are empty and contain no values, formulas or otherwise, and so our protocol is for the rows to be copied so that the newly created rows contain all the formulas contained within that are needed.

As an admin, doing this copy work, multiple times per document (frequently 100 times or more), produces a large number of obstructive warning messages. For regular users this is a complete blocker as in addition to the popup, the formulas in the cells are removed. Rather than necessitate me doing this work for my team, we disable the restrictions so that we can bypass this.

Data Validation does not work this way in excel and other spreadsheet software and it would be helpful if the restrictions are able to look at the returned value, as opposed to the formula itself, to determine if the contained data meets the restrictions

ritzbitz00

https://community.smartsheet.com/discussion/comment/466903#Comment_466903

Hi @Genevieve P. I wanted to follow up on this. This is continuing to be a significant efficiency and data quality blocker for my company, and I am hopeful that this improvement will be considered. I am needing to explain to my leadership team that Smartsheet treats formulated results differently from other common data productivity systems and it may become a deterrent for our continued use of the Smartshet.

Genevieve P.

Hi @ritzbitz00

Thank you for your feedback! While the Product team gathers and reviews information posted in this channel, only the top-voted posts in the Community receive a public status update.

It's definitely interesting to hear how you're using the Restricted values setting and a use-case I'm not personally familiar with. Is there a reason why you can't set a Column Formula on that column? Or have one column with the formula and one with the dropdown values?