have been included in red text and eight blank categories, labeled Additional Cost “Add Categories”, have been included as placeholders. States should update the fields highlighted in red to reflect the state-specific cost categories. Locking and Unlocking Cells and/or Worksheets It is critical to note when filling in the template that only the white cells should contain entered data. The green cells will automatically calculate based on formulas embedded in the spreadsheet. Unless fiscal staff familiar with Excel deem it necessary, do not overwrite or delete the formulas, as this will impact the way the calculations are determined. The template spreadsheet provided as workbook tool six(a) is unlocked so that states can update the cost categories. Once these updates are made, the operating agency should lock the spreadsheet prior to distribution to the local agencies to prevent alteration of formulaic cells. To lock the spreadsheet: Select the tab “Review” Click Sheet” “Protect Ensure that both worksheet and contents of locked cells” and unlocked “Protect “Select cells” are checked Enter a password to unlock the spreadsheet. Note: that this password should be documented by operating agency staff, as the spreadsheet cannot be unlocked without it. Select “Ok” Reenter the password when prompted and select “Ok” To unlock the spreadsheet: Select the tab “Review” Click Sheet” “Unprotect Enter the password created when locking the sheet Select “Ok” To lock/unlock specific cells: Select the cell(s) to lock or unlock Right click and select cells…” “Format Select the tab “Protection” If the cells should be locked, a check mark should appear in the box to the left of the option. If the cells should be unlocked, there should be no checkmark “Locked” Select “Ok”
have been included in red text and eight blank categories, labeled “Add Additional Cost Categories”, have been included as placeholders. States should update the fields highlighted in red to reflect the state-specific cost categories. Locking and Unlocking Cells and/or Worksheets It is critical to note when filling in the template that only the white cells should contain entered data. The green cells will automatically calculate based on formulas embedded in the spreadsheet. Unless fiscal staff familiar with Excel deem it necessary, do not overwrite or delete the formulas, as this will impact the way the calculations are determined. The template spreadsheet provided as workbook tool six(a) is unlocked so that states can update the cost categories. Once these updates are made, the operating agency should lock the spreadsheet prior to distribution to the local agencies to prevent alteration of formulaic cells. To lock the spreadsheet: Select the “Review” tab Click “Protect Sheet” Ensure that both “Protect worksheet and contents of locked cells” and “Select unlocked cells” are checked Enter a password to unlock the spreadsheet. Note: that this password should be documented by operating agency staff, as the spreadsheet cannot be unlocked without it. Select “Ok” Reenter the password when prompted and select “Ok” To unlock the spreadsheet: Select the “Review” tab Click “Unprotect Sheet” Enter the password created when locking the sheet Select “Ok” To lock/unlock specific cells: Select the cell(s) to lock or unlock Right click and select “Format cells…” Select the “Protection” tab If the cells should be locked, a check mark should appear in the box to the left of the “Locked” option. If the cells should be unlocked, there should be no checkmark Select “Ok”
Workbook%20Tool%20Six%20-%20Cost%20Pool%20Guidance%207-21-17.docx