Saturday, November 19, 2011

Part 2: Using the USDA Plant Code Lookup Excel Macro

Edit 4/25/2013:  I updated all spreadsheets to be able to convert Scientific Names to Accepted Plant Symbols, which was a requested feature.  In order to do this, I had to eliminate plant synonyms from the data list. But, I added a few other columns such as Family Common Name, Family Symbol, and Order.  The images below look slightly different than those in the updated spreadsheets.  - Colin

If you downloaded the version of the plant code replacement spreadsheet with macros, you can use shortcut keys to convert between USDA plant codes, common names, latin names, etc.

To see if you have the version with macros, go to the "View" tab of the ribbon and click on "Macros". If "plant_list" macros show up, then you have macros.

The most useful use of this worksheet with macros will be to convert plant symbols in your datasheets with common names, family names, or latin names.  To do this, do the following:
  1. Have the macro-enabled "Plant Code Lookup" file open at the same time your excel worksheet with plant data is open. (Be sure to enable macro content if your security settings are medium or high).
  2. Highlight the columns or cells with plant symbol codes in it.
  3. Press one of the shortcut keys to replace the code symbol with another name, as identified below: 
    • Ctrl+Shift+C = replace code with common name 
    • Ctrl+Shift+F = replace code with family name 
    • Ctrl+Shift+L = replace code with latin name  

Another use of the macros is to replace latin names with common names or replace common names with latin names. There are limitations to the use of these commands as noted below:
    • Ctrl+Shift+O = replace latin with common name.  NOTE:  The entire latin name needs to be written exactly the same as in the "LIST" tab, including the variety and "authority" name.  Suggest you customize the "LIST" tab to your needs.
    • Ctrl+Shift+A = replace common name with latin.  BEWARE: there are common names cross-reference with multiple latin names.  Suggest you customize the "LIST" tab to your needs.

Use the "Macro Test" tab of this worksheet to test how this function works before you do it on your personal data.  You cannot undo changes made by a macro command!

Another note:  If you have another workbook with macros in it that use the same shortcut keys that are used in this macro workbook, pressing the shortcut keys run the other macro.

In this example the rose color columns (A through D) are originally entered  plant species data.  Greenish columns (F thru J) are data altered with macros and ready for pivot tables or sorting. The macro to automatically fill-in the blank cells in columns F and G is found in the "BirdMacroLibrary.xlsm" spreadsheet.

Move to the Next Section (Part 3):

No comments:

Post a Comment