Automation using reference cell

mpiza

Hello everyone,

I hope you're doing well.

I'm facing a difficulty in setting up automation. In the project model we've adopted, one person is responsible for monitoring the project progress, and this monitoring is measured as the stages are marked as Complete. This calculation is straightforward with a formula that displays the result in the Percent column. However, the goal is for this value to appear in the % Complete column in the cell corresponding to the progress monitor. However, as this column is linked to the Project settings, it does not allow formulas.

I'm attempting an automation that would update this cell using the value from the percentage column, which would be kept hidden, only as an auxiliary. However, it seems not to be possible. Is there any way to do this?

I appreciate any help.

Bassam Khalil

Hi @mpiza,

To achieve the desired result in Smartsheet with WorkApps, where you want to automate the update of a "% Complete" column based on a calculated percentage in another column (which is kept hidden), you'll need to use a combination of a helper column, automated workflows, and potentially cross-sheet references if your setup spans multiple sheets. Since direct formula application to a linked "% Complete" column isn't possible, automation can bridge this gap.

Here's a step-by-step approach to setting up an automation to achieve your goal:

Step 1: Setup a Helper Column

• Create a Helper Column: Since you've already set up a column that calculates the percentage complete, ensure this column is used as a "Helper" or "Calculation" column. This column will hold the calculated percentage value that you want to reflect in the "% Complete" column.

Step 2: Use Automation to Update "% Complete" Column

1. Create an Automated Workflow:Go to Automation > Manage Workflows.
2. Click on "Create Workflow" and select "Custom Automated Workflow."
3. Trigger: Set the trigger to "When a row changes" and specify the condition to watch for changes in your Helper (Calculation) column.
4. Action: Choose "Update Row" as the action. Here, you'll specify that the value from your Helper column should be copied to the "% Complete" column. You may need to use a custom action where you specify the source (Helper column) and destination ("% Complete" column).

Step 3: Additional Configuration

• Ensure Proper Permissions: Make sure the automation has permissions to edit the sheet and that the "% Complete" column is not a column that restricts edits due to project settings or dependencies.
• Cross-Sheet References (if necessary): If your project monitoring spans multiple sheets, you might need to use cross-sheet references to pull the calculated percentage into the main sheet before setting up the automation.

Caveats and Considerations

• Automation Limitations: Smartsheet automation can handle a wide range of tasks but be aware of limitations, particularly around how frequently automations run and how they interact with linked columns or sheets.
• Testing: After setting up your automation, test it with a few rows to ensure it behaves as expected, adjusting the trigger and action as necessary.

Alternatives

If direct automation within Smartsheet doesn't meet your needs or you encounter limitations, consider using Smartsheet's API for more complex logic or integrating with third-party tools like Zapier or Workato, which can offer more flexibility in automation but might require additional setup and possibly incur extra costs.