When you have multiple compensation planning programs in one planning model (e.g., bonus, merit, promotion, equity), not all employees will be eligible for all compensation programs. You may setup conditional row locking in SecureSheet to lock the input columns for employees where they are not eligible for a compensation program. You do this by:


  1. Setting up a Locked Cells column in your main SecureSheet tab and adding a formula that checks for conditions and identifies columns to lock on each row, accordingly.
  2. Identifying the Locked Cells column in the properties of each end user view where the locking applies.


For instructions and an example spreadsheet with conditional cell locking setup on the main SecureSheet tab, click here.


  1. Setting up the Locked Cells Column

Use one of the spare columns to the right of your last column of data that will contain the logic to conditionally lock cells in SecureSheet.

  1. Label a spare column "Locked Cells" in the header row.
  2. Use the following formula structure to write the conditional logic that identifies which column(s) to lock. For example, if an employee is not eligible for a merit award, lock the merit input column.


Formula: =IF($L7="N","R"&ROW(),"")

Where:  =IF(value_logic_check_or_checks,"ColumnLetterToLock"&Row()_puts_excel_row_number_reference_into_formula,"else resolves to blank")


When you have multiple locking conditions, use a semi-colon to delineate the end of one condition and the start of another condition.

Formula: =IF($L7="N","R"&ROW(),"")&";"&IF($M7="N","W"&ROW(),"")


When you have a range of columns to lock, follow a similar syntax the identify the range (in this example, columns H through K will lock when the locking condition is met):

For example: =IF(OR($M12="N/A",$M12="Below D",$M12="Not Eligible"),"H"&ROW()&":"&"K"&ROW(),"")


  1. Identifying the Locked Cells Column in View Properties

On each end user view where conditional cell locking applies, identify the Locked Cells Column in the view properties and cells will lock accordingly in SecureSheet.

  1. Enter the following syntax in the Lock Cell Range (use ; as separator) view property:

ROW-S(AB)

Where: (AB) equals the Locked Cells column letter on your main SecureSheet tab

  1. Repeat step 3a on each end user view where cells need to lock conditionally.


Considerations:

You may have different cell locking scenarios for different groups of users. When that is the case, add as many different Locked Cells columns as needed to accommodate each view. The ROW-S(LockedCellsColumn) may be different on each user view, if needed.