Control Center Global Updates: Find / Replace

Find/Replace data in your Control Center portfolio

1. Navigate to your selected program in Control Center, select Manage Program > Global Updates > Find/Replace.
2. Name your update and select the blueprint and template to update.
3. Enter your Find and Replace with values, and then select the columns you would like to update from the In these columns dropdown.
   • Select Advanced Options to add additional conditions to your update. 
   • Select Add Find/Replace to run more than one Find/Replace operation in a single Global Update.
4. Select Create & Run to test your changes. 

Brandfolder Image

5. Complete the Global Update to apply your changes. 

To run a Find/Replace update for Profile Data fields: Select Advanced Options > Add Condition > Select test dropdown > [Row] and in Select condition dropdown > is Profile Data

Restrictions with Find/Replace

Some column types are either unavailable to use Find/Replace or have restrictions. Use the following table for guidance.

Restrictions on Contact List column data

The value in the Find box must either be the full name of the person displayed on the sheet or the email address associated with that contact. You can specify either Joe Smith or joe.smith@mbfcorp.com.

Values in Contact List columns must be exact matches. For the Find value, you must use one of the following in the Restrict to box under Advanced Options:

• is equal to
• is not equal to
• is blank
• is not blank

For the Replace with value, open Advanced Options and set the Restrict to box to replace entire cell. If you use any other values for Restrict to, the update skips Contact List columns but still processes other column types.

Restrictions on Date column data

Cells in Date columns only match the Find value when you've specified one of the following restrictions:

• is equal to
• is not equal to
• is blank
• is not blank

If you specify any other restriction, Date columns are skipped. Similarly, the Replace restriction must use replace entire cell or you'll skip the Date columns.

For example, if you use a Find value of "01/30" and Replace with value of "02/01" while Restrict to is contains, this does NOT match a cell in a Date column that is set to “01/30/2017” because you're using the contains restriction. If you include these values in the update, the operation runs for Text/Number column types.

When you enter a date in the Find or Replace box and select a Date (or Date/Time) column, you receive a warning indicating how the date gets interpreted (for example, February 15, 2018). You then have the option to convert the value to a universal date format (YYYY-MM-DD). 

Converting to a universal date format is a good best practice for matching Date columns; it helps avoid confusion caused by different date display formats (for example, mm/dd/yy or dd/mm/yy).

Smartsheet displays dates according to each user’s personal settings.

Additional ways to use Find/Replace

Use Control Center to perform a partial Find/Replace

Select Advanced Options to display a Restrict to option under the Find and Replace boxes.

You can indicate more advanced rules to set how the Find value matches to cells:

• Matches the entire cell: is equal to (default), is not equal to
• Matches a part of the cell: contains, does not contain, begins with, ends with
• Compares the entire cell: is greater than, is less than
• Other aspects of the cell: is blank, is not blank, is a number

Brandfolder Image

You can specify how the Replace value updates the cell:

• Replace entire cell (default)
• Replace matching text

Control whether Find matches uppercase and lowercase letters

By default, the Find value ignores case. Select the match case option in the Restrict to box to ensure that matching is case sensitive.

Update formulas using Find/Replace

If the Find value begins with an equal sign (=), it matches the formula in a cell instead of the displayed result. Similarly, if the Replace value begins with an equal sign (=), it updates the entire cell with that formula. You can only Find/Replace entire formulas.

You may update a value to a formula, a formula to a value, or a formula to a revised formula.

Limitations on where to use formulas

You receive an error message if you attempt to place a formula in any of the following column types:

• Contact List
• Any of the columns designated as the Duration, % Complete, or Start Date in Project Settings

You can put a formula in the following column types; however, doing so may produce unexpected results:

• Checkbox/Flag/Star - the result of the formula must be true/false
• Date columns - the result of the formula must be a date

Advanced conditions with Find/Replace

You can set conditions that must be satisfied on other columns in a row besides the Find value before the replacement occurs.

For example, you want to update the duration of the Identify key risks task from 4d to 6d, but you don't want to update other tasks that have the same duration. 

Use the following specification:

Find string = 4d, Replace string = 6d and specify an advanced condition that Task Name is equal to Identify key risks.

You can specify multiple advanced column conditions, and you can choose whether all or any of the conditions must be met.

Multi-select columns such as multi-contact or multi-dropdown support Find/Replace.

Using the [Row] condition

With the [Row] condition, you can specify properties of the row that aren't values. You can only specify one [Row] condition at a time.

The following table lists some recommended uses for [Row].

Use Find/Replace with profile data

By default, Find/Replace doesn't make changes to rows in the Summary section of a sheet (where all the profile data is stored).

To make changes to profile data values or formulas in the Summary section (for example, to change the formula for the Overall Project Health profile data), use the Advanced Options section to add the condition [Row] is Metadata. 

When you do this, Find/Replace applies only to the Summary section and skips the rest of the sheet.

Changing the name of a piece of profile data doesn't automatically update the configuration. You need to update changes in profile data names using the Control Center builder.