This tool can be used on your computer at conventions so prospects can type in their information and it will load into an Excel spreadsheet.

A few things to keep in mind:

  • The spreadsheet has two tabs, the first one launches the prospect interface. The second is the spreadsheet where the data is collected. You may want to minimize the Excel spreadsheet before launching the tool. The interface is small and anything behind it will be visible to the user.
  • You must enable macros in Excel in order to use the tool. If Macros are disabled, you will see a warning bar at the top of the spreadsheet. Click on the "Options" button next to the warning message. Then select "Enable Macros."
  • The blue box at the top of the spreadhseet contains the closing message. You may change this message if you like.
  • Prospects will be prompted to enter their email address twice to ensure accuracy. However, there still could be typos in other fields.
  • The form will require an e-mail address, but a dummy address can be used (make sure it is something you will recognize as such so that you delete it from your prospect report).
  • Prospects must fill out every field before pressing "Submit" or they will get an error message.
  • Once the data is collected after a convention, you will need to transfer the information to the prospect form you currently use to send me. You can easily accomplish this by copying each column of data and pasting it in the corresponding column in the form.
  • The phone number is put into the spreadsheet in three fields. These must be combined before they are sent to me. You can combine them using the Concatenate formula in Excel. To do this, create a new column. Make sure the format is set to General. Type into the first cell "=concatenate" then the numbers of the three cells that contain the parts of the phone number. You should see the phone number correctly there. Then paste that cell down the entire list of data. Excel will automatically update to each row and combine the numbers. When you paste that column into the spreadsheet, use the Paste Values function to transfer the numbers, not the formula. (I know this sounds complicated, but once you figure it out, it's quite easy.)

Feel free to play around with this before your convention so you get the feel of how it works. Please let Barb know if you have any questions.

Data Capture Tool

To Change Text to Upper Case (Capitals)

If you have typed your prospect list and realize that you didn't set your caps lock, or if you are compiling your prospect list from the Data Capture Tool and need to change to all caps, you can use the "Upper" formula in Excel.

  1. Insert a column next to the one you need to correct, making sure it is in General format.
  2. In the first cell, enter "=UPPER(cell#)" using the specific cell # that you are correcting and hit return. For example, if you are working on the Wife's First Name and have inserted a blank column B to work in, you would probably be referencing cell A2 for correction.
  3. Click back on the cell you just entered (B2) and the cell will have a box surrounding it, with a small square in the lower right corner. If you click on that square and drag it down the column to the bottom of the data that you are correcting, Excel will copy the formula for those cells.
  4. Copy/paste the column title from the column you were correcting to the top of your corrected data.
  5. You have now created a correctly-formatted column of data which can be used for your prospect list. If you are working from the Data Capture Tool, click and drag to highlight the content of this column, then hit CTRL+C to copy it. Click on the column in your prospect list that you will be filling, now click the drop-down arrow under "paste" in the Clipboard section of your Home toolbar, and select Paste Values. Your all caps data should copy into the column. If you are working directly in your prospect list to correct the format, you will need to insert an additional column to copy/paste the values of your corrected format as directed in this step. When you have pasted the values into the new column, you can delete the original column and the formatting column, being sure to leave the data in the correct column for the prospect list format.
  6. Repeat these steps for each column that needs to be replaced with all CAPS.