Template Details #Formula Row Keyword List

Formula Row Keywords

---

Table of Contents

• Overview
• MatchHeader
• ReplaceZero
• ConvertBooleanToYesNo
• ReplaceZeroOrEmptyWithNoYes
• GenerateRowReference

---

Keywords Overview:

The “Details-Product” worksheet formula row contains predetermined keywords to simplify operations performed by the automation process. Each keyword contains an hard coded operation that performs desired operations. Replace the keyword with any excel formula to override the default behavior.

Below are the list of keywords and their corresponding excel formula equivalents.

MatchHeader:

This keyword replaces Excel formulas such as - INDIRECT("'"&$B$4&"'!"&N$5&(ROW()+3)). This formula copies all data under a specific column name from a source table.

“MatchHeader” keyword will use the reference row’s cell as column name, searching it within a predefined worksheet to copy all data into the “MatchHeader” column.

ReplaceZero:

This keyword replaces Excel formulas such as - IF(INDIRECT("'"&$B$4&"'!"&AB$5&(ROW()+3))=0, "-", INDIRECT("'"&$B$4&"'!"&AB$5&(ROW()+3))) This formula tests if the value under the specified column name equals to zero, if true fill in with “-”, otherwise fills the data in from the source table.

“ReplaceZero” keyword will use the reference row’s cell as column name, replacing any zero value with blank, or the value itself if it is not zero.

ConvertBooleanToYesNo:

This keyword replaces Excel formulas such as - =IF(INDIRECT("'"&$B$4&"'!"&W$5&(ROW()+3))="T","Yes","No") This formula tests if the value under the specified column name equals to T(True), if true fills in “Yes”, otherwise fills “No”.

“ConvertBooleanToYesNo” keyword will convert T with “Yes”, otherwise replaced with “No”.

ReplaceZeroOrEmptyWithNoYes:

This keyword replaces Excel formulas such as - IF(INDIRECT("'"&$B$4&"'!"&W$5&$B3)=0,"No","Yes") or IF(INDIRECT("'"&$B$4&"'!"&AI$5&$B3)=0,"No",IF(INDIRECT("'"&$B$4&"'!"&AI$5&$B3)="","No","Yes")) The above formulas will replace 0 or empty string values with “No”, and other values with “Yes”.

“ReplaceZeroOrEmptyWithNoYes” keyword replicated above functionality.

GenerateRowReference:

Simply Generate Row Numbers based on certain values.