Statement Tab Design

Each statement will have a corresponding tab in SecureSheet.  For consistency and ease of maintenance, the following standards are applied to a new statement design:

  • Columns A through F are the statement content.  
    • Additional columns may be used if necessary to accommodate the content design.
  • Column L is the Show column. 
    • Whether or not a row is shown or hidden on a statement is controlled by the value in the Show column.  If a formula is on a row, it will be highlighted and indicates that there is special scenario logic that will be applied to determine if the row is displayed or hidden. For example: 
      • =IF(AND(ISBLANK(INDIRECT("'Merit'!"&"AZ"&$N$3)),INDIRECT("'Merit'!"&"BF"&$N$3)>0),"Show","")
  • Cell N2 is the Unique ID which gets cross referenced to the ROW ID on the main SecureSheet tab.
  • Cell N3 is the calculated ROW ID on the main SecureSheet tab that tells SecureSheet the specific row to pull values from for the unique statement.  
    • The formula syntax for this cell is:
      • =IFERROR(MATCH($N$2,Merit!$A$1:$A$5000,0),"Not Found") 
        • Where "Merit" equals the main compensation tab name and Column A is the SecureSheet Unique ID.
  • Each unique value in the content of the statement is mapped to a specific column(s) in the main SecureSheet tab using an INDIRECT formula. For example:
    • Concatenate two columns (e.g., firstname lastname) for statement content:
      • =INDIRECT("'Merit Data'!"&"K"&$N$3)&" "&INDIRECT("'Merit Data'!"&"L"&$N$3)
      • Shows as Todd Falco in the statement
    • To ensure cell data is formatted with two decimal places, use the TEXT formula in conjunction with INDIRECT:
      • =TEXT(INDIRECT("'Merit Data'!"&"AP"&$N$3),"$#,##0.00")
      • Shows as $87,000.00 in the statement
    • Column mappings within a paragraph:
      • ="Your earned payout is "&TEXT(INDIRECT("'Merit Data'!"&"O"&$N$3),"$#,##0")&". This amount was derived by multiplying your target opportunity of "&TEXT(INDIRECT("'Merit Data'!"&"P"&$N$3),"$#,##0")&" by the Total Weighted Payout % listed above."


Show Column Example:


Statement Tab Show Column with Show Logic Explained:

Modify the Default View on the Statement Tab

After a statement tab is created, the Default view on the Statement tab must be modified accordingly to support the INDEX/MATCH and INDIRECT formula logic on the statement tab that pulls in unique values for each exported statement:


  1. From the statement tab, select Views.
  2. Select Default from the Views: dropdown.
  3. Turn on the Export Link: checkbox.
  4. Enter P#HideLS in the Export Options: checkbox.
  5. Turn off the following checkboxes:  Row IDs, Col IDs, Grid Lines, and Cell Border.
  6. Enter the last row of content on the statement tab in the Freeze at Row field.  SecureSheet will then be able to automatically calculate all the mappings and applied logic for all of the rows in the statement. 

NOTE:  If you export a blank statement, check that your freeze at row is set to the last row of content on the statement tab.

  1. Put an Advanced Filter on Column-L:  Seq: 1 = Show.
  2. In Summary Calcs, enter:  A1=THIS;K1="#disablescrolling#";L1="#hideheaderrows,L1:L39#"
    • Where hideheaderrows includes all of the rows in the statement and is the same as the "Show" column (in Step 8).
  1. Set every column with statement content to be displayed to Locked.
  2. Set every column without statement content to Hidden.
  3. Click Save View.


Continue to setup the statement on main SecureSheet tab: