Instructions for Editing Values

Instructions for Editing Values for the Public Lands Corps Tracking Form

The Public Lands Corps Tracking form contains the following visible worksheets:

1. Instructions – basic guidance on how to use the worksheet and where to send it.

2. Participant Information – designed to capture information for each Public Lands Corps participant engaged.

3. Project Information – used to collect information for each of the projects

4. Project Tracking – used to track the number of hours each participant has worked on each of the projects tracked.

There are also two hidden worksheets that contain information for data validation.

1. Lists – Contains the values used for data validation – this sheet is normally hidden

The visible worksheets use a feature called Data Validation to ensure that the values entered for certain fields are valid. The fields that currently user validation are as follows.

• Gender

• Ethnicity

• Race - 1

• Race - 2

• Education Level Completed

• Military/Veteran Status

• Partner Organization

• Program Type

• Deputy Area/Office

• Type of Work

• FS Unit (Location)

• Certificate Status

The available values for these fields are listed on a hidden worksheet titles Lists. This worksheet may be unhidden by right clicking on any of the tabs at the bottom of the worksheet and selecting “Unhide” from the menu that appears and then clicking on “Lists” to unhide.

The basic process for modifying these values is as follows:

1. Unhide the Lists worksheet:

   1. Right click on any tab at the bottom of the workbook.

   2. Select Unhide from the pop up menu

   3. Select Lists from the available hidden worksheets

2. Once the Lists Worksheet is unhidden you may change any of the values used for validation.

3. Locate the column that is associated with the validation values you want to change. Let’s say that we want to add an additional selection to the Gender validation field for “Declines to state”. You can simply click on the cell beneath the last entry, type “Declines to state” and press the return key. This value will be listed in the column for Gender as shown below.

4. Once you have the new/modified values in the Lists worksheet, you will need to modify the data validation rules on the entry worksheets (Participant Information, Project Information, and Project Tracking) as applicable.

   1. In our example we will continue to use the Gender field, so you would click on the Participant Information tab and select the first row below title Gender in the worksheet as shown below.

2. Press and hold the SHIFT key, then while still pressing the SHIFT key, also press the END key, followed by the DOWN ARROW. This will select all of the cells that we want to modify the data validation for.

3. Next select DATA from the menu at the top of the workbook, and data validation (highlighted in yellow below) from the Data Tools submenu displayed.

4. The Data Validation menu will appear, click on the icon to the right of the Source field, highlighted in yellow below:

5. This will then take you to the List sheets and display the current selection for validation. We see that the validation is using a range called Gender.

6. Click on the X in the upper right corner to close the Data Validation dialog box. We now need to modify the range to include all of the cells we want to use for validation.

7. Back on the Participants Information worksheet, select Formulas and then Name Manager as shown below.

8. This will display the Name Manger dialog box. Locate the named range you want to update form the list and double-click on it.

9. This will display a list of all the named ranges in the workbook. Select the one you need to modify and double-click on it.

10. You will then see the following dialog:

11. Click on the small spreadsheet icon to modify the selection. This will display the current selection and you can then expand or reduce the selection to include the appropriate cells.

12. Once the correct cells are selected, click close and OK and you will return to the worksheet where you can check that the dropdown now contains the values you specified.

13. The last step is to hide the Lists worksheet. To do that you simply right click on the Lists tab at the bottom of the workbook and select Hide.