Based on how you run your process through your organization, approval locking can be used to control user access based on approval status if desired. If your process requires each approval level to submit/approve before opening to the next level, approval locking can be used to control user access based on the approval status. 


Follow these instructions when there are gaps within the approval chain. For example, if there are 5 levels of leadership, 1 being the top of chain, and a direct manager of an employee is also a level 2 leader and there are no levels between the employee and their level 2 leader, that is referred to as a gap in the approval chain, or skipping levels in the approval process.


When there is a complete hierarchy without any gaps, follow the Multi-Level Approval Locking instructions.


For an example spreadsheet with approvals that skip levels, click here.


Reach out to the SecureSheet Support team if you need assistance with this setup.


SCENARIO: Each Level Submits/Approves Before Opening to the Next Level and the approval locking needs to skip over any missing levels and apply approval logic at the next available level

In this scenario, each level of planners/managers/leaders must submit/approve before the planning data input opens to the next level, but some approval levels may not exist on some employee rows.


Example requirement:  The submit/approval process needs be completed in this order: Direct Manager, Level 2 Approver, Level 3 Approver, Level 4 Approver and Final Approver:

  • Direct Manager submits
    • The data entry fields lock to the Direct Manager and open to Level 2 Approver
  • Level 2 Approver approves
    • The data entry fields lock to the Level 2 Approver and open to Level 3 Approver
  • Level 3 Approver approves
    • The data entry fields lock to the Level 3 Approver
  • Level 4 Approver approves
    • The data entry fields lock to the Level 4 Approver
  • Final Approver approves
    • The data entry fields lock to the Final Approver


Setting up the submit/approval locking:

  1. The Excel model will need submit/approval columns for each level in the hierarchy, separate locking columns and separate name columns for each level.
    • The locking columns will use IF statements based on the status listed in the submit/approval columns and if a name is listed for a level.
  2. The Excel model will also need a column to determine the first approvable level. In this example, the formula is in column P: =IF(G3<>"",2,IF(H3<>"",3,IF(I3<>"",4,IF(J3<>"","Final"))))
    • This IF formula is looking at the Approval Level Name columns to find the first column that is not blank to determine the first approvable level.


  1. In View design in SecureSheet, modify the advanced filter on the approval locking columns you've created:
    1. Assign a Seq: to the advanced filter (the number will vary on how many seq you already have set up)
    2. Select the condition not equal to (<>) to be used for the comparison
    3. List abc as the comparison text
    4. Set to unlocked
    5. Use or as the connector
    6. Select the 2nd condition as In
    7. List the 2nd comparison text as LOCK or whichever word you choose to use within your Excel IF statement for when the row should lock
    8. Set to Locked




Considerations

  • Even if you have a direct hierarchy and SecureSheet is inferring the organization hierarchy, you will want to have a column for each approval level's name.
    • The name field will be used in the IF statement when determining if an approval level should be skipped and moved to the next approval level.
    • These columns are also helpful in determining who is next to approve.
    • List approver's name in their highest approval level column and only share them to that inferred rollup approval view. For example, an end user is a 2nd level approver for some employees and a 3rd level approver for others, so you will want to list them as the 3rd level on all the rows they are typically 2nd and share them to the 3rd level approver view.