Trac^© FAQ’s #26

Can data be re-organized or rearranged on Trac worksheets? Ex. Organized by block, variety, pesticide trade name, spray date, etc.

Yes, data can be re-organized or rearranged on Trac’s data entry worksheets. Use Excel’s Data/Filter or Data/Sort features to re-organize data in Trac. Of greatest utility will be the ability to Filter or Sort data in the SprayData, FertData, and BloomHarvestData worksheets. The data entry worksheets need to be unprotected before being filtered or sorted. All automatically filled report form worksheets are password protected and cannot directly be filtered or sorted.

Filtering Data

Filtering data on a worksheet, for example by a particular variety, will only display the data on that particular variety. Filtering is a safe feature to use because it does not permanently rearrange the file’s data and can be easily turned on and off. Filtered data sets can be printed directly from the SprayData, FertData, and BloomHarvestData worksheets but the auto-filled report form worksheets will not filter when filtering the SprayData worksheet. When printing directly from a filtered worksheet, only the filtered data is printed. In addition, after filtering the data, you can hide columns to further narrow down what would be printed. This allows you to generate customized reports for special reporting needs, such as a report showing spray activity on a particular variety bound for a certain market. See additional information on Filtering Data in Excel’s Help.

For example, filter data to

• Show the sprays applied to a single variety, such as Empire apple on the SprayData worksheet.

• Group the cost of spray applications on a single block, such as the Home Block, for the entire season on the SprayData worksheet. Then select the Total Cost column and Excel automatically sums and displays the overall cost in a gray cell at the bottom of the Excel window.

• Determine the acres and varieties of grapes harvested on a certain day, such as September 15, on the BloomHarvestData worksheet.

• Display spray data entries by Target Pest and then by Farm on the SprayData worksheet to check the use of different chemistries or modes of action to prevent pest resistance.

Filter Data by following the steps below:

Filter data on the SprayData, FertData, or BloomHarvestData worksheets.

Open the worksheet to be filtered by clicking on the worksheet tab. On the main menu click on Tools, then Protection, then Unprotect Sheet to unprotect the worksheet. Click on any single cell containing data or in the colored row of data headings. Click on Data, then Filter, then AutoFilter on the main menu. Arrows appear to the right of each data heading. Click on the arrow in the column containing the information you want to filter and select from the drop-down list the item to filter on. The worksheet now shows just the entries for that item. To filter further, select items from the drop-down lists on other columns. To show all the data again, click on Data, then Filter, then Show All on the main menu. The worksheet can then be filtered for other sets of data. To turn filtering off, click on Data then Filter and click on the checkmark next to AutoFilter. The arrows will disappear from the colored data heading cells. When you have completed your filtering operation and all the data is showing, protect the worksheet again, Tools/Protection/Protect Sheet (without a password) to prevent inadvertent damage to the Trac software worksheet.

Print Filtered Worksheets

Filtering data greatly facilitates printing directly from the data entry worksheets because only the displayed, filtered data will print. Click on File then Print from the main menu and hit OK. If desired, click on the Preview button to verify what will print before clicking on Print in that Preview window.

Generate Customized Reports

Follow the Filter methods above to filter the information by rows. Then hide columns of data you want to eliminate from your report by doing the following. Click on the top gray cell of the column you want to hide to select the entire column. On the main menu click on Format, then Columns, then Hide to hide that column. Continue hiding columns until you have the data set needed for your report. Click on File then Print from the main menu and hit OK. If desired, click on the Preview button to verify and adjust the printout before clicking on Print in the Preview window.

Generate Automatic Processor Reports by Copying Filtered Data into a new Trac file.

You can generate the automatically filled processor report forms for filtered sets of data with copy and paste special/values into a new Trac file or Trac template. Note that paste special/values will overwrite formulas in the light blue shaded cells. After you have filtered the data in either the SprayData, FertData, or BloomHarvestData worksheets, as described above, open a new Trac file. This can be accessed either from the software CD, the computer folder containing the read only Trac software file, or from File then New if you created a template. Save this new, empty file with a convenient file name for the filtered subset of data.

Return to your master file containing the filtered data set. Select the entire range of filtered data by clicking on one corner cell and then holding the Shift key down while clicking on the opposite corner cell. Click on Edit then Copy from the main menu. Open the new Trac workbook and the same worksheet from which you copied the filtered data. Click on the top left cell in the area for data-entry (A3 on SprayData; A6 on BloomHarvestData and FertData) and on the main menu click on Edit then Paste Special. In the Paste Special window click on Values and hit OK. Continue with Copy/Paste Special/Values to copy sets of information from the NameAddress worksheet into the new Trac file in order to automatically fill in this information on the processor forms. Save the file with the filtered subset of data.

Always keep in mind that paste special/values will overwrite formulas in the light-blue-shaded cells. If you want to keep formulas intact, use Copy and Paste; then click Yes to all the message windows that appear. Additional Trac files of subsets of the filtered data may be created as described above to suit your reporting and data archiving needs.

Sorting Data

Sorting data on a worksheet, sorts the rows of data by information in one column and then by the next and then the next, for example by Spray Date, then by Block, then by Variety Sprayed. Data can be sorted alphabetically or numerically in either ascending or descending order. Save your sorted Trac file with a different file name when using Excel’s Data/Sort features in order to preserve the original unsorted data file. When selecting data to sort on the SprayData, FertData, or BloomHarvestData worksheets, make certain that all rows and columns of the entire data set are selected. Do not sort a single column of data because this will disassociate the information in this column from that in the rows. The automatically filled report worksheets will be sorted in the same manner as the SprayData worksheet. See additional information on Sorting Data in Excel’s Help.

For example, sort data to:

• Organize sprays chronologically by Date Spray Applied and then by Farm and then by Orchard or Vineyard on the SprayData worksheet. This may be particularly helpful if a spray was entered out of chronological order or to group sprays by the location applied.

• Organize spray data entries by Pesticide Trade Name and then by Farm on the SprayData worksheet. The seasonal cost of each pesticide on each farm can then be viewed, as follows. Select the Total Cost column entries for a particular pesticide used on a farm and Excel automatically sums and displays the overall cost in a gray cell at the bottom of the Excel window.

• Organize alphabetically the Varieties list column on the FarmData worksheet to make the drop down list on the data entry worksheets easier to scan.

• Organize sprays by Date Spray Applied and then by Variety Sprayed and then by Farm so the automatically filled processor reports can be printed for a particular variety being sold.

Sorting Precautions

In large data sets, never sort just one column of data because this will alter the relationship of the data in that column with the rest of the data in the rows. Use Edit, then Undo to undo OR press and hold the Ctrl key and press Z to undo.

When sorting data on the SprayData, FertData, or BloomHarvestData worksheets, save your farm’s Trac file before sorting data. Save the sorted file to a different file name such as Trac04MyFarmSort and sort only in this file. Open your farm’s Trac file that contains all the data and click on File then Save As on the main menu. In the Save As window, enter a new file name, such as Trac04MyFarmSort and hit Save. The file just saved is now the new, open file. Proceed with sorting as described below.

Sort Data by following the steps below:

Sorting data on the NameAddress and FarmData worksheets.

This would be done in the master Trac file to facilitate data entry. The sets of information for the responsible persons and pesticide applicators on the NameAddress worksheet can be sorted. There might be an advantage, for instance to have the applicators sorted by the expiration date of their pesticide applicator certification license. Or it might prove easiest to scan lists of names that are in alphabetical order. Depending on what you want to sort, select the range of cells containing data in the Responsible Persons section (cells E5 to H8 in TracApple and E5 to H9 in TracGrape) or the range of cells containing data in the Applicators section (cells E10 to H19 in TracApple and E11 to H19 in TracGrape) by clicking on the top left corner and dragging the mouse down to the bottom right corner. Follow the directions for sorting in the section below.

The lists of data on the FarmData worksheet become the drop down lists on the data entry worksheets. Therefore, it may prove useful to sort the FarmData information in alphabetical order to make the drop down lists easier to scan. However, if, for instance, McIntosh is the main variety grown and would be selected most frequently you might want it to remain at the top of the list and therefore there would be little advantage in sorting. If you want to alphabetize any of the lists on the FarmData worksheet the Blocks and Acres columns must be sorted together, all others must be sorted one column of data at a time. Within a column (ex. Farms) click on your first entry in that column and drag the mouse down to select all the entries in that column. Follow the directions for sorting in the section below.

Sorting data on the SprayData, FertData, or BloomHarvestData worksheets.

Open the worksheet to be sorted by clicking on the worksheet tab. On the main menu click on Tools, then Protection, then Unprotect Sheet to unprotect the worksheet. Click on any single cell containing data or in the colored row of data headings. Click on Data then Sort on the main menu. The entire range of data will be selected and a Sort window will open. In the Sort window under Sort by, choose the column heading you want to sort by first from the drop-down list, then choose from the next drop-down list, and then the last, if needed. Next, choose Ascending or Descending to sort alphabetically starting with either A or Z. Hit OK. The range of data will be sorted as chosen. All the automatically filled report forms in this file will be sorted in the same manner as the data in the SprayData worksheet. When you have completed your sorting operation, protect the worksheet again, Tools, then Protection, then Protect Sheet (without a password) to prevent inadvertent damage to the Trac software worksheet. Save the sorted file to a different file name, such as Trac04MyFarmSort. This file can be sorted again at a later date or a new file saved for a different sorting operation.

Print Sorted Worksheets

After sorting, to print the entire set of data in any of the automatically filled report forms, see FAQ #25.

Sorting data will facilitate selecting a particular range of data to print from either the SprayData, FertData, or BloomHarvestData worksheets or the automatically filled report forms that sort with the SprayData worksheet. To do this, select the rows that contain the data you want to print. Click on the top row’s number box at the left hand side of the worksheet window to select the entire row. Then, while holding the mouse button down, drag down to the last row containing the data you want to print. Click on File then Print from the main menu. In the Print window under Print What click on the button to choose Selection and hit OK. If desired, click on the Preview button to verify what will print before clicking on Print in the Preview window. Then select and print any needed cover page information, for instance rows 1 to 26 on the Knouse form, rows 1 to 19 on the Canandaigua Wine form, etc., by selecting those rows and printing as just described.

Alternatively, use either the method described below for sorted data or the method using filtered data described in the section Generate Automatic Processor Reports by Copying Filtered Data into a new Trac file to create and print the automatically filled report forms for just a subset of your entire farm’s data.

Generate Automatic Processor Reports by Copying Sorted Data into a new Trac file.

You can generate the automatically filled processor report forms for sorted sets of data with copy and paste special/values into a new Trac file or Trac template. Note that paste special/values will overwrite formulas in the light blue shaded cells. After you have filtered the data in either the SprayData, FertData, or BloomHarvestData worksheets, as described above, open a new Trac file. This can be accessed either from the software CD, the computer folder containing the read only Trac software file, or from File then New if you created a template. Save this new, empty file with a convenient file name for the sorted subset of data.

Return to your master file containing the sorted data set. Select the desired subset of sorted data by clicking on one corner cell and then holding the Shift key down while clicking on the opposite corner cell. Click on Edit then Copy from the main menu. Open the new Trac workbook and the same worksheet from which you copied the sorted data. Click on the top left cell in the area for data-entry (A3 on SprayData; A6 on BloomHarvestData and FertData) and on the main menu click on Edit then Paste Special. In the Paste Special window click on Values and hit OK. Continue with Copy/Paste Special/Values to copy sets of information from the NameAddress worksheet into the new Trac file in order to automatically fill in this information on the processor forms. Save the file with the sorted subset of data.

Always keep in mind that paste special/values will overwrite formulas in the light blue shaded cells. If you want to keep formulas intact, use Copy and Paste; then click Yes to all the message windows that appear. Additional Trac files of subsets of the sorted data may be created as described above to suit your reporting and data archiving needs.