Control Center Global Updates: Find / Replace

Find/Replace Global Updates updates values in project sheets and templates. To use Find/Replace, provide a Find value and a Replace value.

By default all columns will be updated. To limit which columns will be updated, use In these columns to limit the update.

To Find/Replace data in your Control Center portfolio:

1. Navigate to Control Center > Manage Program > Global Updates and select  Find/Replace.
2. Name your update, and then select the blueprint and template you want to use.
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. You can also select Add Find/Replace to run more than one Find/Replace operation in a single global update 
4. Complete the Global Update to test and apply your changes. See Test and Validate Your Changes for more information. 

NOTE: To run a Find/Replace update for Profile Data fields, select Add Condition in Advanced Options, then select [Row] in Select test and set the condition to is Profile Data

Restrictions with Find/Replace

Some column types are either unavailable to SCC Find/Replace or are available, but with restrictions. These column types are listed in the following table.
 

 

Restrictions on Contact List Column Data

The value in the Find box must either be the full name of the person that is displayed in 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:

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

For the Replace with value, the Restrict to box in Advanced Options must be set to replace the entire cell. If you use any other values for Restrict to, Contact List columns will be skipped (but other column types will be processed).

When using Find/Replace with Contact List columns, you can use either the email address or contact name. Global Updates will replace the entire contact (name and email).

Restrictions on Date Column Data

Cells in Date columns will only match the Find value when one of the following restrictions are specified:

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

If any other restriction is specified, Date columns will be skipped. Similarly, the Replace restriction must use replace the entire cell or Date columns will be skipped.

For example, if using a Find value of "01/30" and Replace with value of "02/01" while Restrict to is contains, this will NOT match a cell in a Date column that is set to “01/30/2017” because the contains restriction is being used. If  these values are included in the update, this operation will be run for Text/Number column types.

When you enter a date in the Find or Replace box and a Date (or Date/Time) column is selected, you’ll receive a warning indicating how the date will be interpreted (for example, February 15, 2018). You’ll 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 can help you avoid confusion caused by different date display formats (for example, mm/dd/yy or dd/mm/yy).

Dates in Smartsheet are displayed according to each user’s personal settings. Date display is controlled at the user level in Personal Settings. For more information about date formats, see Changing the Default Date and Number Format (via the Language Setting).

Additional ways to use Find/Replace

To use Control Center to perform a partial Find/Replace

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

You can indicate more advanced rules on how the Find value will be matched to cells:

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

You can specify how the Replace value will update the cell:

• Replace the entire cell (default)
• Replace just the matching text

To control whether Find matches uppercase and lowercase letters

By default, case is ignored when matching the Find value. To ensure that matching is case sensitive, select the match case option in the Restrict to box.

To update formulas using Find/Replace

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

A value may be updated to a formula, a formula may be updated to a value, or a formula may be updated to a revised formula.

Limitations on where formulas can be used

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

• Contact List
• The column designated as the “Duration column” in Project Settings
• The column designated as the “% Complete column” in Project Settings
• The column designated as the “Start Date column” in Project Settings

You will be able to 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

To update all the formulas when the row numbers in the formulas are different

For information about working with formulas in Control Center, see Formula Requirements in the Control Center Global Updates help article.

Advanced conditions with Find/Replace

You can set conditions on other columns in a row that must be satisfied in addition to the Find value before the replacement will occur.

For example, if the duration of a specific task (“Identify key risks”) needs to be updated from 4d to 6d but you want to ensure that other tasks that have the same duration are not updated. Use the following specification:

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

Multiple advanced column conditions may be specified and you can choose whether all the conditions must be met or any of the conditions must be met.

Find/Replace is supported on multi-select columns such as multi-contact or multi-dropdown.

Using the [Row] condition

The [Row] condition allows you to specify properties of the row that are not values. Only one [Row] condition can be specified at a time.

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

 

When you want to do this	Use this value for [Row]
Update values or formulas that are different based on their hierarchy level	has Children has no Children has Parent s Level 1 is Level 2 is Level 3 is Level 4
Update locked or unlocked rows only	is Locked is not Locked
Indicate that the Find/Replace is applied only to profile data (see the related question below)	is Metadata

To use Find/Replace with profile data

By default, Find/Replace will not 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, the Find/Replace will be applied only to the Summary section—the rest of the sheet will be skipped.

Note that changing the name of a piece of profile data will not automatically update the configuration. Changes in profile data names will need to be updated using the SCC Builder.