AX4000
When using the Update Persistent Plan Files processing mode for Process Plan Files, the settings for the update operation are defined in a report file with a special control sheet of PlanFileReconfig_ControlSheet. This topic is a reference for the settings in this control sheet, and also contains some configuration examples.
Axiom Formula Fix settings
The PlanFileReconfig_ControlSheet has 5 Axiom Formula Fix sections that can be used to define search-and-replace operations for existing plan files. To use a section, set Activate to On and then complete the settings in that section.
| Item | Description |
|---|---|
|
Activate |
Specifies whether the section is active. If On, the settings in this section will be applied when Update Persistent Plan Files is run. If Off, the settings in this section are ignored. |
|
Target worksheet in plan files to update |
The name of the sheet in the plan files to update. Does not apply when the action is Simple Find/Replace Entire Workbook. |
|
Column to search in the target sheet |
The column that contains the search criteria. Only applies when the action is Exact Match, Partial Match Value, or Partial Match Formula. This can be a different column than where the fix needs to be applied. For example, you may need to search for a word, value, or formula in column P to find the correct row, but the actual update is applied to a different column or columns in the same row, such as AI:AT. If the update location is not in the same row as the search criteria, you can use the offset setting. |
|
Row number to begin searching in the target sheet |
The row in which to begin searching, within the specified search column. Only applies when the action is Exact Match, Partial Match Value, or Partial Match Formula, and is optional in these cases. This setting allows you to skip the "setup" areas at the top of the sheet, if appropriate. |
|
Criteria to find separated by a pipe in search column of target sheet |
The text, value, or formula to locate in the target column. Does not apply when the action is Specific Address. You can specify multiple search criteria, separated by the pipe character. If multiple search criteria are specified, they are treated as an OR statement (meaning, the cell only has to match one of the criteria). |
|
Action to find search criteria |
Specifies how the search finds matches. Select one of the following:
|
|
Column range to apply new formula if criteria is met |
Specifies the column range in which to apply the update if the search criteria is found. Only applies when the action is Exact Match, Partial Match Value, or Partial Match Formula. For example, specify P:P to make the change in column P only, or specify P:Z to make the change in a range of columns. By default, the update is applied in the row where the search criteria is found, unless a row offset is specified in the Offset this number of rows field. |
| Enter the specific cell reference |
Specifies the cell to update when using the Specific Address action. Enter the cell reference in the format A1. The sheet for the cell reference is the specified target worksheet. NOTE: This is the same field as the Column range to apply new formula if criteria is met field. The text of the field changes when Specific Address is the selected action. |
|
Text of formula to copy |
Defines the text, value, or formula to copy to the update destination in plan files. Only the cell contents are copied, not the formatting. If you want to copy formatting as well (or by itself), then use the Copy the format from this cell to the destination field. If you want to copy a formula to plan files, this cell must contain the formula string formatted as text. By default, the cell is formatted as Text so that the formula will not resolve. For example, the contents of the cell should look something like the following, instead of a resolved formula: =ROUND(V{0}*(1+AD{0}),0) To copy a formula from another spreadsheet (such as the revised template), you can place your cursor in the cell, highlight the contents of the formula bar, and then copy and paste the formula string to this cell. Any variable references to rows in the formula should be replaced with a row offset in curly brackets. For example, D{0} is the current row, D{-4} is four rows above the current row, and D{4} is four rows below the current row. |
|
Copy the format from this cell to the destination |
Specifies whether formatting is copied to the update destination. By default, this is set to No, which means that the existing formatting of the update destination is preserved. If you want to change formatting in plan files, select one of the following and then format this cell as desired:
This option does not apply if either of the "simple find / replace" actions are used. IMPORTANT: The formatting is copied from this cell, not from the Text of formula to copy cell. If you are updating cell contents and format, then the contents go in the "text of formula" cell and the formatting goes in the "copy the format" cell. |
|
Offset this number of rows below the search criteria to apply the fix |
Optional. Specify the number of rows below the search criteria in which to apply the update. For example, you might search for the text Statistics and want to apply the update to a column location that is two rows down instead of in the current row. If the update location is above the search criteria, you can enter a negative number. An entry of 2 applies the fix 2 rows below the search criteria, whereas an entry of -2 applies the fix 2 rows above the search criteria. Only applies when the action is Exact Match, Partial Match Value, or Partial Match Formula. |
|
Continue processing multiple instances of the search criteria when it's found (advanced) |
Specifies whether the update process continues after the first match or stops. By default, this is set to Yes, which means the update process looks for all eligible matches and updates each one. If set to No, then the update process stops after updating the first match. Does not apply when using either of the Simple Find/Replace options, or when using Specific Address. |
Example 1: Exact Match
The Exact Match option finds the search criteria in the specified column and applies the update to the specified location. It searches values for exact matches and is case-sensitive.
The following example searches for the text "Revenue" in column S, and then replaces the formula in column AA on the same row. The formula uses the variable {0} for the current row number:
=ROUND(X{0}*(1+AF{0}),0)
Where {0} is replaced with the current row number when the update occurs.
Example Exact Match configuration
The following example shows how the update is applied in a plan file:
Example updated plan file
Example 2: Partial Match Value
The Partial Match Value option finds the search criteria in the specified column and applies the update to the specified location. It searches values for partial matches.
The following example searches for the text "Research" in column S, and then replaces the formulas in columns AK:AV on the same row. The formula uses the variable {0} for the current row number:
=ROUND($AA{0}*IF($AH{0}="Base Period",BN{0}, INDEX(AL$27:AL$36,$CA{0})),0)
Where {0} is replaced with the current row number when the update occurs.
Example Partial Match Value configuration
The following example shows how the update is applied in a plan file:
Example updated plan file
Example 3: Partial Match Formula
The Partial Match Formula option finds the search criteria in the specified column and applies the update to the specified location. It searches within formulas for partial matches.
The following example searches for the partial string "=ROUND(X" within formulas in column AA, and then replaces those formulas in the same column and row (search column and update column range are the same column). The formula uses the variable {0} for the current row number:
=ROUND(X{0}*(1+AF{0}),0)
Where {0} is replaced with the current row number when the update occurs.
Note that you need to change the number format of the search criteria cell to Text so that you can enter the partial formula string as text (otherwise it will resolve as an invalid formula).
Example Partial Match Formula configuration
The following example shows how the update is applied in a plan file:
Example updated plan file
Example 4: Simple Find/Replace
The Simple Find/Replace options find all instances of the search criteria and replace it. These options search within values for partial matches. The Simple Find/Replace Target Worksheet option only updates the specified target worksheet, whereas the Simple Find/Replace Entire Workbook option updates the entire workbook.
The following example searches for the text "Supplies" within the target worksheet, and replaces it with the text "Materials". Note that the search column, search row, and update column range fields are left blank since they do not apply in this case. If the action was Simple Find/Replace Entire Workbook instead, then the target worksheet would be blank as well.
Example Simple Find/Replace configuration
The following example shows how the update is applied in a plan file:
Example updated plan file
Example 5: Specific Address
The Specific Address option finds the designated cell address and replaces the contents of it. This option is for cases where you know the specific location of the fix and it is the same location for all plan files that you need to update.
The following example finds cell R67 on the Budget sheet and replaces its contents with the text "Sales and Marketing". Note that the search column, search row, and search criteria fields are left blank since they do not apply in this case, and the column range field has been replaced by the cell address field.
Example Specific Address configuration
The following example shows how the update is applied in a plan file:
Example updated plan file
