Formula preventing manual entry to cell. Is there a possible workaround?

mbsamuel6

Maybe a little long, but some backstory is necessary to get a better understanding on what's going on and if I'm approaching this issue right.

Currently my Smartsheet is setup where there is a formula in the "Status" column that checks if there are less then 2 names in the "Total Emails per Application" Column.

The "Total Emails per Application" column gets its number from the User Emails + Emails to Add - Emails to Remove columns.

In the screenshot above, say there are two users in the "User Emails" and "Total Emails per Application" field. If a user would type in one name in the "Emails to Remove" field and change the status to "certified" then that means there is now one name in the "Total Emails per Application" field.

We don't want that because we don't want a user to be able to have one person assigned to the "Total Emails per Application" column, still be able to change the status to "certified" and click save.

I don't believe there is a way in Smartsheet to prevent a user from saving in these types of situations. For what its worth the person that requested this project wanted a way to prevent users from having the option to select "certified" unless that "Total Emails per Application" number was two or higher.

As a work around I put a formula in the Status column that reads:

=IF([Total Emails per Application]@row < 2, "Additional Research Needed", "")

However, that formula for some reason is preventing me a person who has admin rights to the smartsheet unable to modify the Status column. Notice in the status column above, its blank and I can't change the values in that field at all until I remove the formula.

Also, the formula above, I originally had it as

=IF([Total Emails per Application]@row < 2, "Additional Research Needed", "Certified")

That was great, but the problem was that it auto changed the status column to "Certified" or "Additional Research Needed" thus preventing users the option to select the status.

Hope all this makes sense.

So question is am I approaching the issue the right way or is there a better to write the formula I have in the status column?

Andrée Starå

Hi @mbsamuel6

I hope you're well and safe!

You can't change it because you're using the Column Formula feature.

As a possible workaround, you could change it back to a cell formula column and make the change, but then all rows are editable again.

Make sense?

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

mbsamuel6

@Andrée Starå 

I think you are on to something. That helped. Didn't know you could make a cell column formula.

So here's the weird part. In the Dynamic view the status column is still read-only for some reason. I can edit the status column field in the actual Smartsheet thanks to making that formula a cell column formula.

But for some reason in the Dynamic view its still read only. And also when I send an update request with the status column, the update request sends a blank field.

I disabled all the logic and made sure the status column is off of read-only in Dynamic Views.

So does the cell column formula prevent users from updating the field in dynamic view and Update Request?

mbsamuel6

I just tested this and the column formula does prevent users from updating the status column in dynamic views and update request.

So I think the best thing to do is create multiple IF statements within the status column that will change the status to "Certified" or "Additional Research Needed" depending on the number of emails.

The only problem with doing it this way is that the formula will mark everything as "certified" before the users can change anything when they receive the update request. So I'm not sure what the best way to do this.