Block cells in a column from being updated until another column has been updated first

dred

I have two columns, Status and Customer Update. I would like to prevent users from changing the status of a request without entering a customer update first. Is this possible?

jmyzk_cloudsmart_jp

Hi @dred

If you're looking to make sure your team only takes action after a customer has submitted feedback, here are a couple of ways to manage that in Smartsheet—whether or not your staff has direct access to the sheet.

Solution 1: Sheet Not Shared with Assigned Staff

If your sheet is not shared with the assigned team member:

Idea: Use an Update Request triggered when a customer provides feedback.

• When the Customer Update column is updated (i.e., not blank), send an Update Request to the person in the "Assigned To" column.
• In the request, do not include the “Customer Update” field itself.
• Customer Update: {{Customer Update}}
• Allow the assigned staff member to respond with comments and attachments via the update request.
• This way, the staff doesn’t need access to the full sheet but can still take action and provide updates securely.

Received Update Request Image

Solution 2: Sheet Shared with Assigned Staff (Editor Access)

If your sheet is shared with the assigned staff:

Idea: Use automation to control when they can edit a row.

• Create a workflow automation that locks the row when:
  • A new row is added, and
  • The Customer Update column is blank.
• Add another workflow that:
  • Unlocks the row when the Customer Update column is not blank or has changed.
  • Sends an alert to the "Assigned To" person so they know it’s time to respond.

This approach ensures that staff can only edit rows after customer feedback has been received, keeping the process controlled and organized.

https://app.smartsheet.com/b/publish?EQBCT=76544f793bdc4a01b5b6bd9350db1f45

Lock Row Automation

Unlock Row Automation

dred

The staff on my team do have access. If I create an automation that locks rows when the customer update column is blank, how will they be able to enter the customer update if it's locked? The row will only be unlocked once the automation is triggered, but this will not occur until an update is entered.

Is there something I am not seeing?

I believe in my case, I need an automation that locks a particular column based on certain conditions, but I'm not sure if this is possible.

jmyzk_cloudsmart_jp

Hi @dred

I assumed the customer updated the Customer Update, not your team.

If your team member updates both the Customer Update and Status in that order, we can not use the above approaches; we can use other methods.

Conditional Formatting

Highlight the Status column of rows where Customer Update is blank (acts as a visual warning). (The alternative will be to highlight rows where the Status is updated but the Customer Update is blank.)

You can not stop your team member from updating the Status column, but the conditional formatting will give a visual warning.

https://app.smartsheet.com/b/publish?EQBCT=8a7c194478064899bd64758342c4739a

Prefilled form and cross sheet reference form

In this method, your team member will not directly update the Status but will use a form to update it.

The image below shows that a form URL with prefilled values in the Update Status column will be displayed when the Customer Update column is not blank.

[ Update Status]

=IF(NOT(ISBLANK([Customer Update]@row)), FormURL# + SUBSTITUTE("?Row ID=" + [Row ID]@row + "&Item=" + Item@row + "&Customer Update=" + [Customer Update]@row, " ", "%20"))

https://app.smartsheet.com/b/publish?EQBCT=7c4a09c2deaa4732bbe16276b21951eb

For example, when I click the Test 3's Update Status URL, the following form will be shown;

The form submission will go to another sheet, as shown below.

https://app.smartsheet.com/b/publish?EQBCT=0ed873073f3642829c7aaf2c2927dafe

The sheet has the [Latest] check box to determine which row is the latest input of a given Row ID with the following formula;

=MAX(COLLECT(Row:Row, [Row ID]:[Row ID], [Row ID]@row)) = Row@row

In the first sheet, we can use the following formula to get the latest Status.

=JOIN(COLLECT({Form Sheet: Status}, {Form Sheet: Row ID}, [Row ID]@row, {Form Sheet: Latest}, true))

Please refer to the following help article for more information on prefilled forms.

https://help.smartsheet.com/articles/2478871-url-query-string-form-default-values