Statement Logic on the Main SecureSheet Tab

On the main SecureSheet tab, the first five columns are reserved to plug in statement logic that is required to export statements in conjunction with the statement form view on the main SecureSheet tab and the default view on the statement tab.

  • Column A is the SecureSheet Unique ID (no leading zeros). 
    • This value is passed to the statement view through the INDEX/MATCH on the statement tab so that the row values are pulled into the export.
    • Column Title: Unique ID
    • Formula (example): =F10*1
  • Column B is the Export Statement Link. 
    • This is a hyperlink that places an Export Statment link on each row, which allows the export of an individual statement.
    • Column Title: Export Statement / Export Letter
    • Formula (example):
      • =IF(logic that removes the statement functionality for any employee row that should not have an associated statement,"","=IF(C7<>"","sv_statement//"&C7&"//Export Statement&PDFID="&D7&"&SO=Y","")
      • For example:
        • =IF(K7<>"","sv_statement//"&C7&"//Export Statement&PDFID="&D7&"&SO=Y","")
          • C7 is the view name that is referenced to export the statement content applicable to this row, with the data row identified (e.g., 7).
          • Export Statement is the text that will display for the hyperlink to export an individual statement from column B on each row. This may be customized to your preference.
          • D7 is the file name of the individual PDF file that will be exported as identified in Column D on your main SecureSheet tab.

NOTE:  This should resolve to "Export Statement" if the employee is receiveing a letter and blank if the employee is not receiving a statement. You will not see "Export Statement" in Excel as this syntax is custom to SecureSheet.

  • Column C is the form view name in the main SecureSheet tab that will export the statement content. If you have more than one statement, the formula in this column needs to identify which statement form the employee needs to receive.
    • Column Title: Form View Name
    • Formula:
      • =IF(logic that removes statement functionality for any EE row that should not have an associated statement/specifies which statement format applies to this row,"Statement1","Statement2")

NOTE:  This should resolve to the PDF file name if this employee is receiving a statement and blank if not. If the employee is not receiving a statement, this needs to be blank or a statement will be generated for them.

  • Column D is the PDF File Name for the exported statement.
    • This can be a concatenation of any columns in the main SecureSheet tab and any text to help easily identify the statements when exported individually or in a batch.
      • Formula (example): =IF(logic that removes statement functionality for any EE row that should not have an associated statement,"",K29&"_"&M29&"_"&"2021 Compensation Statement")

NOTE:  This should resolve to the PDF file name if this employee is receiving a statement and blank if not.

NOTE:  PDF file names need to be unique. If an employee is receiving more than one statement, the statement name must be unique for each statement.

Additional Notes about Statement Exporting:

NOTE:  Formulas in columns B, C and D should resolve to blank for employees that will not be receiving a statement, based on a condition(s) in the data. These formulas need to remove the statement logic on a row so that they will not export in a batch nor individually when an employee is not receiving a statement.

NOTE:  If every employee is receiving a statement, then the IF logic is not required. The “resolve to blanks” is needed when everyone is not receiving a statement and you do not want letters exported with zero values as it may confuse managers. Some clients choose to export anyway to use the letter as a reminder tool that a conversation needs to occur with each employee.


Continue to setup the statement on the main SecureSheet tab: