Lab Create: Protecting Sheets

Estimated reading time: 8 minutes

Overview:

INTERJECT provides an improved method to handle protecting sheets. The Sheet Protector window allows INTERJECT users with the Client Admin role to quickly protect or unprotect the sheet without any need to know the password. The password is default to a very long text string and is managed centrally by the INTERJECT client profile. By doing this, users no longer have to remember passwords and they can be more complex to improve security.

When a sheet is protected using the Sheet Protector window, it will first attempt to use the current password. If that attempt fails it will try any previous passwords that are on file. If those do not work, it will also attempt to use a blank password.

In the following lab, you will protect a sheet and use the INTERJECT pull to illustrate how protected sheets continues to function properly. In this lab you use the Customer Aging report

Unprotecting Cells for User Input:

INTERJECT requires the user to have one of the following roles for your company. This removes the need to set a password.

  • Client Admin
  • Protector
  • Editor

Protecting the sheet still allows for pulls, drills, and many other types of INTERJECT formulas and features. A crucial part of INTERJECT is to still allow users to utilize the input fields built into reports. Protecting the sheet with INTERJECT allows for user inputs to remain unlocked regardless of the user’s role.

One way of doing this is to first select cells C17, C18, C19.


Right click and select Format Cells.


A new window will pop up. Select Protection and uncheck Locked then click OK.


Another way to leave filter cells unprotected is to use the Allow Users to Edit Range tool in the Review ribbon in Excel.


A window will appear, select New


In the title field, type Filters then in the Refers to cells field, select cells C17, C18, C19 then click Ok then Apply.


Note: If the password field is left empty for the named range then anyone can use the filters for the report but they cannot change the data outside of the filter range.


Protecting the Sheet and Pulling Data

INTERJECT’s Sheet Protection tool has three options, Unprotect, Protect, and Cancel.

  • Protect has additional options, which includes choosing to protect the current sheet or the whole workbook.
  • Unprotect will unprotect the current spreadsheet if the user has the correct role within our company.
  • Cancel will close INTERJECT’s Sheet Protection tool.

Navigate to the Interject Ribbon and select Sheet Protector then select Protect.


To verify that the filter cells are not locked, input Market into the Company Name parameter t hen pull the report.


The speadsheet should look like this after.


Now if someone were to try and alter your data, for instance, changing the CustomerID of one of your records, they cannot since they do not have your user role.


Now in cell C19 input SAVEA to verify that the input filters are not protected and pull the report.


Multiple Sheets:

Setup a grouping for columns I:L by selecting the columns, then navigating to the Data Ribbon and select Group.


Now, hold down the shift key and select CustomerAccountDetail to select five different sheets.


With these selected, navigate back to the INTERJECT ribbon and protect the sheet


This will protect all five of the sheets but leave sheets that were not selected alone.

Special Consideration for Column Groupings

The INTERJECT Sheet Protector also allows for groupings to be expanded and contracted


Note: INTERJECT leverages the existing sheet protector in native excel. For this reason, most of the restrictions and rights are the same.

Directly after protecting a sheet, groupings are still operational.


However, closing and reopening a protected sheet with groupings in it will render the groupings locked. If you select the grouping toggle then it will be protected.


There are two ways to fix this.

Option 1:

Pulling the report will refresh the protection settings. One of the settings that are refreshed is the protection of groupings. After a pull, groupings are unlocked while the rest of the report is still locked.


Option 2:

The other way is to navigate to the INTERJECT Ribbon and click on the Advanced Menu to enter the advanced menu.


Select the system dropdown and click Re-enable Protecting Groupings


Now, the sheet is still protected but groupings can function as normal.