Shareware and Freeware Programs for Microsoft Excel

Home Page

Download / Purchase

Macro Support

Excel Related Links

Sponsored Links

Recent Programs

XLEcom Ecommerce Website Creator

Multiple Price Options Add-to-Cart Form Utility

Multiple File Import / Export Utility

Data Sheet

Newsletter Generator Utility

MySQL Database Backup Utility

Drupal CMS Theme Generator

Drupal File Comparison Utility

Drupal Members List PHP Script Generator

Sponsored Links

Older Programs

Database "Plus"

Database "Standard"

Schedule "Plus"

Schedule “Standard”

Form Print Spooler

Credit Card Program

Financial Analysis of Life

Email Generator Utility

Bulk File Generator

Phone / Email Book

Convert Cell References

Mathematical Summary Utility

Extra Utility Tools

Open & Save Utility

Sponsored Links

Database “Plus

Download “Plus” Version 8.3 Now

Register it for $33.00

This Database "Plus" program is identical to the Database “Standard” Program offered except that the “Plus” version includes an additional Email Generator Utility. If you do not need the email feature, then definitely try out the newly revised Database “Standard” Program instead.

Note: Also check out the newly released Data Sheet Program. It is similar to the Database program but is is more streamlined without the built-in analysis tools.

Features

  • Includes a New powerful Email Generation Utility
  • Easy-to-use interface for both non-experienced and experienced database users
  • Supports Multiple Integrated Database sheets.
  • Data Manager with built-in Analysis Tools
  • Create "Date Enabled Databases"
  • Includes Special Utilities for Data Analysis
  • Automatically generate Period Reports (Weekly, Monthly, Quarterly, Biannual, and Annual)
  • Perform Mathematical Analysis with Summaries
  • Dynamic Charting including Animation Capabilities
  • Add a Company Name Banner to Brand your Product
  • Background Error Checking Features
  • Accommodates databases up to 30 field columns wide by 65,531¹ rows
  • Includes full detailed instruction with illustrations.

Brief Description

This Database "Plus" program is identical to the Standard Database Program offered except that the “Plus” version includes an additional Email Generator Utility.

This is a general all purpose Database Spreadsheet Program with Integrated Dynamic Charting Capabilities. It will support both multiple independent and dependent databases (linked sheets) in a single workbook. It was designed for simple functionality and incorporates an easy-to-use interface for both experienced and non-experienced database users. A user can simply manage the data and also create impressive Data Analysis reports utilizing the built-in reporting features.

Key Features of the Email Generator Utility:
By selecting up to 65,536² email addresses from within any spreadsheet, you can:

  • Instantly create mass distribution direct email files for use with Microsoft® Outlook Express or any other email client program.
  • Export the email addresses to Comma Separated list files which can later be imported into your email client’s address book.

Uses:

  • Business: Generate mass distribution direct emails for both current and potentially new customers
  • Members and Groups: Email group members about news and activities.

Full Description
Click here to view the full description in Adobe Acrobat format.

The following information contains both the description of the program and instruction on how to use it. Dialog Box screen shots are also supplied to help you understand the functionality of the program.

Note: All the information within this document is also available within the Database Program. Refer to the Help and Information section of the program.

Introduction:
This is a general all purpose Database Spreadsheet Program with Integrated Dynamic Charting Capabilities. It will support both multiple independent and dependent databases (linked sheets) in a single workbook. It was designed for simple functionality and incorporates an easy-to-use interface for both experienced and non-experienced database users. A user can simply manage the data and also create impressive Data Analysis reports utilizing the built-in reporting features.

The advantage of this spreadsheet database program compared to other types of conventional database programs is that you can have both "formulas" as complex as you want, and also static values as "data" in the database. By using multiple databases (databases on multiple sheets), and linking them together through the use of formulas and functions, you can create a well-integrated relational database. If you decide to use multiple linked databases, it would be a good idea to also create a summary database sheet that you can link to a graphical chart. A chart will always give you a better overall picture of your data.

"It is very important that you first experiment with the sample data that was initially set up in this program so that you can feel confident about the functionality of the program before creating your own database."

If you happen to encounter any problems or bugs in the program, please report them.

Table of Contents:

Introduction

Program Description and Notes

  • Menu Bar Addition
  • Database Template
  • Terminology
  • Company Name on Database Header (Banner)
  • Encountering a Macro error
  • Calculation Speed Concerns
  • Important Rules and Guidelines
  • Initial Setup
  • Multiple Database Sheets

Instructions on how to use the Database Program

Custom Button Bar Summary

   Setup Command / Dialog Box

  • Database Header
  • Sheet Options
  • Clear the data off the active Database sheet
  • Clear the data off the embedded Chart
  • Create New Generic Database Sheet
  • Create a New Date Enabled Database Sheet
  • Convert a Generic DB to a Date Enabled Database
  • Program Settings
  • Check if using Excel version 97, 2000, or greater
  • Turn OFF Calculation Mode Temporarily
  • Turn OFF Auto-Zoom to Fit Screen
  • Turn OFF Auto-Column Width
  • Turn OFF Auto-Row Height
  • Date / Time Stamp
  • Automatic Formatting Options
  • Turn OFF Auto-Borders
  • Turn OFF Auto-Font
  • Error Checking - Database Clean Up

   Utilities Command / Dialog Box

  • Convert Number Formats
  • Convert Cell References
  • Example Situations
  • Data Conversion

   Import Command

  • Importing Data into the Database or Upgrading
  • Software Upgrades

   Math-Info Command
   Display Command / Dialog Box
   Styles Command / Dialog Box
   DataCheck Command
   DataForm Command / Dialog Box
   DataSort Command / Dialog Box
   Duplicates Command / Dialog Box

  • Instructions
  • How the Subroutine works

   Reports Command / Dialog Box

  • Reporting Instructions
  • Analysis ToolPak

   Subtotals Command / Dialog Box

  • Instructions

   DynaChart Command / Dialog Box

  • Chart Functions Button Descriptions
  • Charting Notes
  • Features of the Dynamic Chart
  • Display Options

   Save Command
   End Command
   Help / Information Command / Dialog Box
   About Command / Dialog Box
   Registration Dialog Box

Program Description and Notes:

This Database program has been tested in Microsoft Excel versions 5.0, 7.0 (Excel 95), and 9.0 (Excel 2000) US versions.

(Even though this program was not initially tested in Excel 97, there have been no reported errors or bugs from the many users who are currently are using previous released versions of this program with Excel 97.)

Minimum Display Resolution: 800 x 600 x 256 colors
Recommended Display Resolution: 1024 x 768 x 24 bit color or better

Menu Bar Addition:
An additional drop down menu called "Database" has been added to the original standard menu bar along the top of the sheet. This custom menu addition will only stay active while the database program is the active sheet. This custom menu will be removed and the standard one will be restored when you close the database program.

The purpose of this custom menu bar is to allow the user the convenience to use all the database commands (same as the button functions) even when the buttons are out of view. This will help prevent unnecessary horizontal scrolling.

Database Template (Not necessary, but may be useful):
You may want to save this program as a template file (Database.xlt) for future use. By saving this program as a template file, and then copying the file to your Templates subdirectory (usually located under the Microsoft Office subdirectory), you will have the option to create a new database workbook when you choose File New… from the Excel menu.

To create a Database Template file, you must first disable the splash screen that is displayed when you open this file. To disable the splash screen, reopen this file and check the box on the splash screen "Do not display this message at start up". Next, save this file as an Excel template file by choosing the File - Save as... menu command and select the Template (*.xlt) option from the "Save as type:" scroll box. This should automatically save the file as Database.xlt in the Templates subdirectory. If for some reason it was not saved in the Templates subdirectory, then move the new file to the Template subdirectory manually. The next time you choose File - New… from the Excel menu, you will be given the choice to create a new Database Workbook.

Terminology:
 The term called "Active Sheet " is used throughout this program and can be found within the documentation, dialog boxes, and alert messages. This "Active Sheet" term refers to the current active sheet that is being displayed on the screen at the particular moment. For example, say your database workbook contains four individual database sheets named sheet1, sheet2, sheet3, and sheet4. If you are currently displaying sheet2, then this sheet would be considered the Active Sheet. Any operation or function launched from the Active sheet will be executed on that particular sheet. All the custom database functions within this program were designed to operate on the current active sheet.

Company Name on Database Header (Banner – Refer to the Registration dialog box section):
If you would like to place a permanent non-editable banner onto the database sheets so that your company name always appears within the Database Header, place a check mark in the Registration Dialog box. This optional feature is available so that one can either personalize or semi-protect this program from unauthorized distribution once it has been registered. This "permanent" banner will be placed on all database sheets created in the future within this workbook and cannot be removed or changed by anyone, even by the registered user! During the Registration process is the only time you will be able to make the banner a permanent non-editable banner. Once you register this program, you cannot go back and make changes.

To correctly apply the Company Name banner, you must only have "one database sheet" within this workbook. This single database sheet will act as the Master database sheet in which all new database sheets you create in this workbook will use as a design template. For instance, when you create a new database within this workbook, the header properties of the Master database sheet including its banner is used as a template and therefore transferred to the new database sheet. Although you will be able to change the graphic headers and the database titles, you will not be able to change the banner.

Make sure that this database sheet is the active sheet before running the registration routine. If you do have more than one database sheet, remove them. Otherwise, the default header stating "Your Company Name" will appear on some of the database sheets and also may appear on new database sheets you create within this workbook.

If you do not want to make the banner a permanent non-editable banner, leave the check box unchecked. In this case, any user will be able to change the contents of the banner at any time through the Setup command / Dialog box.

Encountering a Macro error:
 If you happen to encounter a macro error during one of the macro routines, you will be prompted to either continue or halt the macro. The best choice is to continue the macro. You do not have worry about your data, it will be safe. The macro subroutine programs never alter the data in the database. The only automatic changes to the database are cell-formatting changes, which are completely controlled by the user.

This program has been extensively tested for errors. If a macro error is encountered, this would usually indicate an error in the database data itself. Please check your database data for errors before reporting the errors. If you happen to encounter any problems or bugs in the program that are beyond your control, please report them and include any macro prompt messages. Also, is very important that you include both the program version number and the version of Microsoft Excel you are using.

If for some reason you happen to utilize a function that causes the database to appear corrupted, just press the DataCheck button. This should restore the database to its original layout format.

Calculation Speed Concerns:
You may need to utilize the following two features if your database becomes too large, resulting in slow performance and extended processing time.

1. Turn OFF Calculation Mode Temporarily (an option located under the Setup Command menu). Use the OFF mode to speed up operations. The calculation mode is turned off temporally during certain operations while executing the subroutine programs in the background. Once these subroutine programs are finished, the calculation mode is turned back on and all sheets get recalculated. By turning off calculation mode temporally, the subroutine running time is significantly reduced. The check box will still remain checked, but keep in mind that the affect only occurs during the subroutine operations.

2. Remove the data from the embedded Dynamic Chart (a feature located under both the Setup and DynaChart Command menus). If the database program becomes too sluggish or if the calculation time is still too long, you may want remove the data from the embedded chart to prevent the chart from automatically being refreshed in the background.

Important Rules and Guidelines:
Do not enter data into rows 1 & 4 or into columns 1 & 32 (columns A and AF). You can however enter data into rows 2 and 3 except for cells B2 and E2. These particular cells (except B2 and E2) are user editable cells and have been formatted as red colored bold fonts to indicate "user editable" cells. It is very important that you do not edit or clear cell E2 (Database Item Counter). The subroutines programs use this particular cell.

In order to properly retain the database range, rows 1 & 4 and columns 1 & 32 (columns A and AF) are automatically cleared. If you enter data into the areas, you will lose it!

This Program was designed to support a maximum of 30 columns. Do not try to insert more columns. The subroutine programs will not support them. If you only need to use for example, 10 columns for your database, then use the first 10 columns. Try not to leave blank fields and columns between other data columns.

Do not delete, cut, or insert any rows or cells within rows 1 thru 5. Do not delete columns 1 thru 32. If you do, you will destroy the functionality of the database program!

Initial Setup:
(All buttons and commands apply to the current "active" sheet.)

To enter in your own data, you must first delete the sample data.

Automatic Deletion Method:
Use the Sheet Options function under the Setup Command menu to clear the sample data.

Manual Deletion Method:
Select the rows "below" row 6 (rows 7 thru the last row that contains data) and delete them. Only row 6 will remain. Next, clear the data in row 6. You are left with the header rows (rows 1 thru 5) and one database row (row 6). You can also use the button labeled "DataForm" to enter, modify, or delete database entries.

Note: If you are going to delete the "ALL" the sample data or "ALL" the data on another database sheet "manually", remember never to "delete" row 6; you can however "clear" the data from the cells.

You may delete row 6 only in the case that the database contains more than one entry (more than one row of data). After the deletion of row 6, the database must still retain at least one row of data!

Multiple Database Sheets:
If you want to have more than one database, do the following:

Automatic Database Creation Method:
Use the Sheet Options function under the Setup Command menu to create a new database sheet.

Manual Database Creation Method:

  1. Right click on the database sheet name tab.
  2. Choose "Move or Copy".
  3. Select the check box called "Create a Copy" in the dialog box.
  4. Rename the newly copied sheet.
  5. Delete the old data from the database leaving only row
  6. That's it. (Reference. Initial Setup section above)

Database Sheet Deletion:
     To delete a sheet, do the following:

  1. Right click on the database sheet name tab.
  2. Select "Delete" to delete the sheet.

Field Names:
Change the Field names in row 5 to any names you want. These are the Database Field Names also referred to as being located within the Header.

Instruction on how to use the Database Program:

"It is very important that you first experiment with the sample data that was initially set up in this program so that you can feel confident about the functionality of the program before creating your own database."

Detailed instructions on the use of the individual database functions are located within the "Custom Button Bar Summary" section.

Step 1. Clear the Sample data from the database:
Use the Sheet Options function under the Setup Command menu to clear the sample data from the database sheet.

Step 2. Enter your data into the database:
If you want to use the Report feature function within this program, your database must be setup as a "Date Enabled Database". If you need to convert your current database to a "Date Enabled Database", utilize the conversion routine within the Setup menu.

Use the DataForm to enter your data into the database. Keep in mind that you can have both formulas and also static values as "data" in the database. If you decide not to use the DataForm for entering a new database item (an entire row of data), then enter the new data starting immediately after the last database row. When finished, use the DataCheck function to make the new data become part of the database.

Note: It is highly recommended to use the DataForm for new database entries, because formulas contained in the cells are automatically copied to the new database row. In other words, if some of the cells in a database row contain formula functions, then using the DataForm for new database entries will automatically copy the formulas functions from the last row in the database to the new row items. Also, you will notice when using the DataForm, that cells which contain formula functions, will display the calculated values of the cells in the form and will not appear as editable data entry edit boxes.

That’s basically it! The rest of the functions within the program allow you to maintain the database and perform Data Analysis.

Special Features for Data Analysis

Using the Database Filters:
The filters are the little gray down arrows attached to each database field name. When data is filtered, the arrows turn to a blue color. You can do an impressive analysis using both the subtotals and filters at the same time, then if you want, chart the results.

Generating Period Reports:
Use this Period Reports feature to generate weekly, monthly, quarterly, biannual, and annual period reports. The Period Report feature automatically utilizes both the Subtotal and DynaChart features at the same time to create reports that include charts of the summarized data. This is a useful and powerful tool to analyze you data.

If you want to use the Report feature function within this program, your database must be setup as a "Date Enabled Database". If you need to convert your current database to a "Date Enabled Database", utilize the conversion routine within the Setup menu.

A Date Enabled Database is a database that utilizes the first two field columns in the database as date field columns. The first field being a "Month" field and the second field being a "Date" field. The Reports function generates reports utilizing both of these date columns. Note: The sample database that was initially included in this program was a Date Enabled Database.

Generating Charts:
The DynaChart function controls the Integrated Dynamic Charting Capabilities. It allows you to view the data within the database in graphical format, making it much easier to comprehend the data.

To get the most benefit from the charting capabilities, use the charts in combination with the Subtotals and filters. You can generate impressive data summary charts utilizing the Subtotal features.

Custom Button Bar Summary:

Note: All buttons and commands apply to the current "active" sheet.

Setup Command / Dialog Box:

The following features are contained within the Setup Dialog Box

Database Header:

Database Title:
The title text you enter in this section will be displayed along the top left corner of the active Database Header. It is automatically retrieved from the active database when you select the Setup command.

Database Banner:
The banner text you enter in this section will be displayed along the entire active Database Header. It is automatically retrieved from the active database when you select the Setup command. To make this a permanent feature, refer to the Registration dialog box section.

Sheet Options:

Clear the data off the active Database sheet:
Choose this option to clear the data of the active database sheet. The field names will still remain.

Clear the data off the embedded Chart:
Removes the data from the embedded Dynamic Chart (a feature also located under the DynaChart command menu). If the database program becomes too sluggish or if the calculation time is too long, you may want to remove the data from the embedded chart to prevent the chart from automatically being refreshed in the background.

Create New Generic Database Sheet:
Choose this option to create a new generic database sheet without any data.

Create a New Date Enabled Database Sheet:
Choose this option to create a new "Date Enabled" database sheet without any data.

If you want to use the Report feature function within this program to generate weekly, monthly, quarterly, biannual, and annual period reports, your database must be setup as a "Date Enabled Database".

A Date Enabled Database is a database that utilizes the first two field columns in the database as date field columns. The first field being a "Month" field and the second field being a "Date" field. The Reports function generates reports utilizing both of these date columns. Note: The sample database that was initially included in this program was a Date Enabled Database.

The values in the "Date" field column should correspond to your data. It is not necessary to edit the values in the "Month" field column. The "Month" field column contains formulas that convert the dates in the "Date" field column to months using number formatting attributes.

If you want create a Date Enabled Database, be aware that you can only utilize 28 of the 30 field columns for your data since 2 field columns will be needed for the Month and Date fields.

Analysis ToolPak:
If the custom dates or functions within the cells on the spreadsheet appear as #NAME?, then you will have to enable Excel's "Analysis ToolPak". If this feature is not enabled, you can manually enable it through the Excel's Tools menu. To enable the Analysis ToolPak, go to the Tools menu, click Add-Ins. If Analysis ToolPak is not listed in the Add-Ins dialog box, click the Browse button and locate the drive, directory, and filename for the Analysis ToolPak add-in. Next, select the Analysis ToolPak check box. If you cannot find the Analysis ToolPak Add-in, run Excel's setup program to install it and repeat the above instructions. Note: Add-ins you install in Microsoft Excel remain active until you remove them.

Convert a Generic DB to a Date Enabled Database:
Converts the current Database to a "Date Enabled Database". This Database conversion routine will shift all the current database data including the field names over to the right by two field columns so that it can add two date fields in the first two field columns of the database. In other words, the routine will move all the data from database field columns 1 through 28 to field columns 3 through 30.

Since the database conversion requires two additional field columns, you must have the last two database columns (29 and 30) free of any data in order to proceed. You can either move the data in the last two field columns to another location within the database or else remove them by clearing their appropriate cells. (Do not delete the columns; just clear the data from them.)

At the completion of the conversion, you should change the values in the new "Date" field column to dates that correspond to your data. It is not necessary to edit the values in the new "Month" field column. The "Month" field column contains formulas that convert the dates in the "Date" field column to months using number formatting attributes. You can now use the Reports feature function to generate weekly, monthly, quarterly, biannual, and annual reports.

If you had any database field columns hidden prior to the conversion, you may need to unhide the database columns to view the new results. Use the Display option to unhide the database field columns.

Program Settings:

Check if using Excel version 97, 2000, or greater:
Checking the check box results in an increase the processing speed of this program if used in Excel versions 8.0 (Excel 97), 9.0 (Excel 2000), or greater. This feature is only to be used for Excel versions 8.0 (Excel 97), 9.0 (Excel 2000), or greater and is not needed for Excel versions 5.0 or 7.0 (Excel 95). Checking the check box disables some of the auto-intelligence and auto-sensing background functions used within this program. If left enabled (unchecked) in Excel 97 or Excel 2000, the processing speed of the overall program will be exceptionally slow. Excel 5.0 and 95 are not affected in terms of processing speed by the auto-intelligence and auto-sensing background functions and thus the box should remain unchecked.

Turn OFF Calculation Mode Temporarily:
Use the OFF mode to speed up operations. The calculation mode is turned off temporally during certain operations while executing the subroutine programs in the background. Once these subroutine programs are finished, the calculation mode is turned back on and all sheets get recalculated. By turning off calculation mode temporally, the subroutine running time is significantly reduced. The check box will still remain checked, but keep in mind that the affect only occurs during the subroutine operations.

You may only need to use this feature if your database becomes too large, resulting in slow performance and extended processing time.

If the database program becomes too sluggish or if the calculation time is still too long, you may want to also remove the data from the embedded chart so that the chart is not constantly being refreshed in the background. To remove the chart data, use either the Sheet Options feature called "Clear the data off the embedded Chart" in the Setup menu or the "Remove Chart Data" feature within the DynaChart Command menu.

Turn OFF Auto-Zoom to Fit Screen:
Checking the box will turn OFF this feature. As the database values change, so will the column widths. This feature automatically fits the entire database columns within your screen so that you will not have to scroll to left and right as often.

Turn OFF Auto-Column Width:
Checking the box will turn OFF this feature In the ON mode (unchecked), the column widths are automatically adjusted to fit to the size of the largest string of data found within the cells. If you do not want the column widths to automatically resize, then turn OFF this feature.

Turn OFF Auto-Row Height:
Checking the box will turn OFF this feature. In the ON mode (unchecked), the row heights are automatically adjusted to fit the height of data found within the cells. If you do not want row heights to automatically resize, then turn OFF this feature.

Date / Time Stamp:
If you want to apply a Date / Time stamp to the active sheet when you save the document using the custom Save command, then check this box. The stamp will be placed in cells F2 thru H2 underneath the graphic header. To view the Date / Time stamp, you will have to remove the graphic header.

Automatic Formatting Options:

Turn OFF Auto-Borders:
Checking the box will turn OFF this feature. In the ON mode (unchecked), the program will automatically apply grid line borders around all the cells in the database. If you do not want the automatic gridline borders applied to the database, then turn OFF this feature.

Turn OFF Auto-Font:
Checking the box will turn OFF this feature. In the ON mode (unchecked), the program will automatically apply the same font properties to all the data in the database. If you would like to have different font properties throughout the database then turn OFF this feature. The default font when this option is ON is Arial size 10.

Error Checking - Database Clean Up:

This routine will check for and clear all nonblank cells that appear to be blank but actually contain spaces that are not visible to the eye. Otherwise, if left alone, these nonblank cells containing just spaces will count as valid data cells within the database.

This Error Checking - Database Clean Up routine will run automatically in the background if the " Run Error Checking in the Background" box is checked. On slower computers, the time it takes for this routine to complete may be noticeable. If you do not want to have this routine run in the background, uncheck the "Run Error Checking in the Background" box.

If you decide not to run the error checking routine in the background, you can still check the database for errors periodically by placing a check mark in the "Run Error Checking in the Background" check box and then run the "Database Check" routine. Once finished running the "Database Check routine, you can then uncheck the "Run Error Checking in the Background" check box.

Utilities Command / Dialog Box:

The following features are contained within the Utilities Dialog Box

Convert Number Formats:
To quickly change the number format of an entire database field, select an option button that corresponds to the desired field. You will then be prompted to choose or create a new number format. Finally, press the "Update Number Formats" button.

Convert Cell References:
This routine allows the user to convert the cell references of the formulas within the selected range of cells to any form of absolute reference. This can be useful in situations where you have two or more database sheets linked together via formula functions. Keep in mind that you can do a compounded conversion by running this routine multiple times on the same set of cells to achieve the desired results. To view all the formulas on a sheet at once, press the Control and ` keys at the same time. Repeat the keystrokes to toggle back to the previous view.

Example Situations:
1. If you need to have two synchronized sheets linked together, in this case the first sheet contains values, and the second sheet contains formula functions based on the first sheet; and you want or need the capability to sort the first sheet and have the second sheet updated correctly, then you need to convert the formula references on the second sheet to $A$1 format. If you don't convert the references to $A$1 format, sorting the first sheet will cause the formula references on the second sheet to refer the location the original cells independent on the contents of referenced cells.

2. For example, if you plan to do any copying, cutting, and pasting between two sheets and need to retain the formula links between the sheets, then you will need to add the sheet name to the formulas in the source sheet prior to copying, cutting, and pasting. After adding the sheet names to the formulas, you may also need to convert the formulas a second time to $A$1 format.

Data Conversion:
The following functions allows the user to format any desired range of cells. Select a formatting option and then press the "Next>>" button for further instructions.

  • Text numbers to Real numbers
  • Real numbers to Text numbers
  • Remove extra spaces from text strings
  • Text to Upper Case
  • Text to Lower Case
  • Text to Proper Case

Import Command:

Instructions for Importing Data into the Database or Upgrading

Option 1. Import Data into the Active Database Sheet:
This routine will allow you to import data from another spreadsheet into the "active" database worksheet. Keep in mind that all the present data contained on the active database worksheet will be completely replaced with the new data imported from the other spreadsheet.

If you do not want to replace the data on the active database worksheet, then create a new database worksheet using the function within the Setup menu (sheet options), and use this new database worksheet for the data import routine. Remember that you must make the new database worksheet the active worksheet before running this routine.

Note: The cells of the first row of the data you select to import into the database will be the database field names! (30 columns maximum)

Remember that you may need to clean up the imported data. Use the Data Conversion Utility.

Option 2. Software Upgrade:
Imports all the Data from a previous or current version of this program. (This option is only available for individually customized programs)

In order to perform the upgrade, the sheet names of the file to be imported must be the same as the names in the original shipped file! The import routine should go smoothly if the database you will be importing is not subtotaled, filtered, or contains and hidden database columns. Note: Only registered versions of this program can be upgraded.

Warnings: (Applies to Importing Data into the Database)

Keep in mind that if you transfer more than 30 columns, only the first 30 columns will be part of the database range and that the 31st column will be automatically erased. This is done so that the extra data will not interfere with the automatic database seeking routine.

Maximum Columns - If the data range on the spreadsheet you want to import has more than 30 columns, it will not fit onto the database sheet. Reduce the new data selection size to fit within 30 columns.

Maximum Rows - In Excel 95 (version 7) you cannot import more than 16,380 rows (16,384 - 4 header rows). In this case, reduce the new data selection size to fit within the 16,380 rows. You may proceed if you are using a version of Excel greater than Excel 95 (version 7.0) that supports more the 16,384 rows. If your Excel version is not greater than Excel 95 (version 7.0), and the data exceeds 16,380 rows, you will get an error!

Math-Info Command:

This Mathematical Summary utility will allow the user to instantly generate, with the option to print, a mathematical summary of any selected range of cells from either the database or any other sheet. The Mathematical Summary will display both basic math and statistical results. The following are the calculated displayed results:

Basic Math Results: Count of Numbers, Most Common Value, Minimum Value, Maximum Value, Median Value, Average Value, and Summation (total).

Statistical Results: Product (all numbers multiplied), Greatest Common Divisor, Least Common Multiple, Sum of Squares, Standard Deviation (sample), Standard Deviation (population), Variance (sample), Variance (population), Geometric Mean, Harmonic Mean, Kurtosis, Skewness of the Distribution, Sum of Squares of Deviations, and Average of the Absolute Deviations of Data Points from their Mean.

Display Command / Dialog Box:

The following features are contained within the Display Dialog Box

Allows you to:

  1. Display and or hide any or all of the 30 database columns.
  2. Display, not to display, or change the graphic database header.
  3. Display or not to display the Button Bar (Database function buttons)
  4. Restore / Add the Database menu to the Excel Menu Bar along the top. (Only remains active when the database program is in use)

This Program was designed to support a maximum of 30 columns. Do not try to insert more columns. The subroutine programs will not support them.

Do not delete any of the Database columns. If you do not need to use all 30 columns, then use the " Display function to hide the ones you do not need; otherwise use the extra columns for comments (i.e. Comments 1, Comments 2, etc..)

Styles Command / Dialog Box:

The following features are contained within the Styles and Colors Dialog Box:

  1. Change the format style of the active database by utilizing built-in Style templates.
  2. Quickly to change the color attributes of the active database through an easy to use dialog box menu interface.

DataCheck Command:
This function automatically selects and redefines the entire database range then shows the user which field and data cells were included in the defined database. It was made available to the user primarily as a tool to view the defined database range. This Database Check (DataCheck) routine is also incorporated into other functions and is automatically executed in the background.

You can use this function to set and view the defined database range if you decide to manually add data to the database on the spreadsheet itself. For example, if you add a new row to the bottom the database. If you use the DataForm feature to manage the database, it is not necessary to run the DataCheck function.

Do leave an entire blank row in the database. The database must be contiguous. In other words, the database must have at least one entry in each row. If you do not need a specific database item, then delete the row by hand or use the DataForm function to delete it.

DataForm Command / Dialog Box:

Use this dialog box interface to manage the database entries

Special Notes:
Data cannot be entered into columns that are hidden. If you need to enter data into these hidden columns, first unhide them using the Display function, then use the DataForm for data entry. You can then hide the columns after the data entry is finished.

New database entries using the DataForm are added to the bottom of the database.

If you decide not to use the DataForm for entering a new database item (an entire row of data), then enter the new data starting immediately after the last database row. When finished, use the DataCheck function to make the new data become part of the database.

It is highly recommended to use the DataForm for new database entries, because formulas contained in the cells are automatically copied to the new database row. In other words, if some of the cells in a database row contain formula functions, then using the DataForm for new database entries will automatically copy the formulas functions from the last row in the database to the new row items. Also, you will notice when using the DataForm, that cells which contain formula functions, will display the calculated values of the cells in the form and will not appear as editable data entry edit boxes.

DataSort Command / Dialog Box:

Use this function to sort the database using up to three criteria fields.

Duplicates Command / Dialog Box:

Use this routine to find and or delete all duplicate entries within the database. The duplicates found will be highlighted in a different color.

Instructions:
Fill out the dialog box starting with Step 1 and finishing with Step 3. If you want to watch the step-by-step process of this routine, check the "Watch the routine in action" check box.

Note, In order to watch the process, the program will have to refresh the display each time a duplicate entry is found and or is deleted. This display refreshing will cause the routine to slow down. If your database contains many line items, or if your computer is too slow, you may not want to watch the process, because it may take some noticeable time to complete.

How the Subroutine works:
Once you press the OK button, the following process occurs:

First the database is sorted based on the following keys.

Key #1 = Primary Search Criteria Field (Step 2a)
Key #2 = Additional Search Criteria Field (Step 2b)
Key #3 = Additional Search Criteria Field (Step 2b)< BR >

If the Additional Search Criteria Fields are not used, the database is sorted using only the Primary Search Criteria Field.

Second, after sorting the database, the find and or delete process starts from the first row of the database and ends at the last row of the database. During the search, the computer compares Row X with the next row, Row (X+1) based on the search criteria. Row (X+1) will be the row deleted if you chose to run the delete duplicate entries routine.

This is why you should choose the proper sort order before pressing the OK button!

Duplicates entries are determined by comparing the concatenated criteria cells in the both the active row (Row X) and in the row directly below (Row (X+1)). If an exact match is found then both rows are considered to be duplicates. Keep in mind that there must be an exact match independent of the cell formatting.

Concatenation is defined as stringing the contents of multiple cells together.

For example:
   Cell 1: Focus
   Cell 2: 33.21
   Cell 3: P-12345

Concatenating Cells 1 thru 3 will yield: Focus33.21P-12345

If the value in Cell 2 was actually 33.211154 but formatted to be displayed as $33.21 on the sheet, then concatenation of Cells 1 thru 3 would yield: Focus33.211154P-12345. In other words, if the criteria in Row X to be evaluated contained the actual value of 33.21 and the criteria in Row (X+1) contained the actual value of 33.211154, then this would not be considered an exact duplicate. Both these values may appear to be the same when formatted on the sheet as $33.21, but are not considered by the search routine to be same value.

If you are doing a text comparison, make sure that the text strings do not contain extra blank spaces. To remove the extra blank spaces from the text strings, run the Data Conversion utility within the Utilities function prior to running this Duplicates routine.

Reports Command / Dialog Box:

Use this Period Reports feature to generate weekly, monthly, quarterly, biannual, and annual period reports. The Period Report feature automatically utilizes both the Subtotal and DynaChart features at the same time to create reports that include charts of the summarized data. This is a useful and powerful tool to analyze you data.

If you want to use the Report feature function within this program, your database must be setup as a "Date Enabled Database". If you need to convert your current database to a "Date Enabled Database", utilize the conversion routine within the Setup menu.

A Date Enabled Database is a database that utilizes the first two field columns in the database as date field columns. The first field being a "Month" field and the second field being a "Date" field. The Reports function generates reports utilizing both of these date columns. Note: The sample database that was initially included in this program was a Date Enabled Database.

Reporting Instructions:

To generate reports:

  1. Select a Report Type
  2. Select the Subtotal Fields
  3. Select a Subtotal Field Function
  4. Choose a Chart Type and Size
  5. Press the "Generate Report" button.

To restore the database:
Use the "Restore Database" feature to restore the database back to its original format when you are finished generating the reports.

Analysis ToolPak:
If the custom dates or functions within the cells on the spreadsheet appear as #NAME?, then you will have to enable Excel's "Analysis ToolPak". If this feature is not enabled, you can manually enable it through the Excel's Tools menu. To enable the Analysis ToolPak, go to the Tools menu, click Add-Ins. If Analysis ToolPak is not listed in the Add-Ins dialog box, click the Browse button and locate the drive, directory, and filename for the Analysis ToolPak add-in. Next, select the Analysis ToolPak check box. If you cannot find the Analysis ToolPak Add-in, run Excel's setup program to install it and repeat the above instructions. Note: Add-ins you install in Microsoft Excel remain active until you remove them.

Subtotals Command / Dialog Box:

This is one of the best features of this database program. You can generate both sub and grand totals for any column in the database. Once you have created the subtotals, you will see some strange looking buttons created on the left side of the database. These new buttons are called outlines. The main buttons along the top labeled 1, 2, and 3 are used for collapsing the subtotaled data by category. Use them; they can be very helpful in analyzing your data.

Use Subtotals in combination with the filters. The filters are the little down arrows attached to each database field name. When data is filtered, the arrows turn to a blue color. You can do an impressive analysis using both the subtotals and filters at the same time, then if you want, chart the results.

Notes:
If you decide to use the subtotals in combination with the filters, you may want to generate the subtotals first, and then use the filters. If for some reason you lose or remove the filters, you can restore them by selecting the first Field Name (cell B5) and choose from the top menu: Data - Filter - AutoFilter.

Instructions:

To generate subtotals:

  1. Select the database field by which you want the subtotals to change.
  2. Select the actual database fields you want subtotaled.
  3. Select from the Subtotal Function scroll box a function type that represents the subtotal type.
  4. Finally, press the Create Subtotals button.

If you want to have a Chart generated with the Subtotals (see the Additional Options), then you first need to set up the Chart parameters using the DynaChart function prior to generating the Subtotals. Otherwise, if you don’t set up the parameters, the Chart that will be generated will utilize the last set of Chart parameters, which may not necessarily represent the data you want to plot.

To remove previous subtotals, press the Remove Subtotals button.

DynaChart Command / Dialog Box:

The DynaChart function controls the Integrated Dynamic Charting Capabilities. It allows you to view the data within the database in graphical format, making it much easier to comprehend the data.

To get the most benefit from the charting capabilities, use the charts in combination with the Subtotals and filters. You can generate impressive data summary charts utilizing the Subtotal features.

Chart Functions Button Descriptions:

Generate Chart:
Generates a new chart or replaces the previous data in the current chart with the new selected field data.

Animate Chart:
Rotates the chart dynamically 360 degrees. Only works for specified chart types as indicated by an asterisk (*). Useful for viewing the all the data plots within a multiple series 3D chart. Can also be useful for setting up the chart in a desired perspective view for printing. After the animation begins, you may stop it at anytime prior to it finishing on its own by pressing the Escape Key.

Display Chart:
Displays the last generated chart

Hide Chart:
Hides the chart from view so that the database can be seen.

Print Chart:
This function gives the user the option to print the chart in either Black and White or in Color.

Remove Chart Data:
Removes the data from the embedded Dynamic Chart. If the database program becomes too sluggish or if the calculation time is too long, you may want to remove the data from the embedded chart to prevent the chart from automatically being refreshed in the background.

Charting Notes:

This Database program was designed to always retain a dynamic chart on each of the database sheets even though it may not be in view.

It is not necessary to delete the chart, just hide it from view using the "Hide Chart" function if you do not need to view it.

You can removes the data from the chart so that the chart is not constantly being refreshed in the background. If the database program becomes to sluggish or if the calculation time becomes too long, remove the chart data by pressing the "Remove Chart Data" button.

Keep in mind that you can always customize the chart by double clicking it or by right clicking the mouse over it. To edit a particular item or section, double click or right click the mouse again directly over the item or section.

Remember to first unselect the chart if you want to use any of the database functions.

Features of the Dynamic Chart:
The dialog box interface will allow you to plot up to four (4) series on the chart. You can however add as many additional series plots to the chart by doing the following:

  1. Select the database column(s) you want to add to the chart. This would mean selecting both the field names and all the data in the particular columns.
  2. With the mouse placed over the selected data, click and drag the selected range of data directly over the plot area of the chart, then release the mouse button. This will instantly add the data to the chart. Next, you may be prompted on how you want the data to appear. That's it!

Display Options:
If you want to hide a particular series (plot) on a chart that contains multiple series, use the Display function to hide the database field column that corresponds the data for that particular series. You can use the Display function as sort of a toggle for displaying the series you want to view.

Save Command:

Use this function to save the database with a Date / Time stamp if the option was previously set in the Setup dialog box. Otherwise, you can use the File-Save command from the menu.

End Command:

Closes the database workbook.

Help / Information Command / Dialog Box:

Use this function to view this information within the Database program. You can also register the program from within here by pressing the "About" then "Register" buttons.

 

About Command / Dialog Box:

This is where you can get general information about the program and also the Payment and Registration details.

Register Dialog Box:

The Registration feature is accessible through the About Command / Dialog Box. This is where you register the program and will also have the option to permanently lock the company name banner onto the database sheets.

Do you want to permanently lock the Company Name Banner on the Database Header ?

If you would like to place a permanent non-editable banner onto the database sheets so that your company name always appears within the Database Header, place a check mark in the Registration Dialog box. This optional feature is available so that one can either personalize or semi-protect this program from unauthorized distribution once it has been registered. This "permanent" banner will be placed on all database sheets created in the future within this workbook and cannot be removed or changed by anyone, even by the registered user! During the Registration process is the only time you will be able to make the banner a permanent non-editable banner. Once you register this program, you cannot go back and make changes.

To correctly apply the Company Name banner, you must only have "one database sheet" within this workbook. This single database sheet will act as the Master database sheet in which all new database sheets you create in this workbook will use as a design template. For instance, when you create a new database within this workbook, the header properties of the Master database sheet including its banner is used as a template and therefore transferred to the new database sheet. Although you will be able to change the graphic headers and the database titles, you will not be able to change the banner.

Make sure that this database sheet is the active sheet before running the registration routine. If you do have more than one database sheet, remove them. Otherwise, the default header stating "Your Company Name" will appear on some of the database sheets and also may appear on new database sheets you create within this workbook.

If you do not want to make the banner a permanent non-editable banner, leave the check box unchecked. In this case, any user will be able to change the contents of the banner at any time through the Setup command / Dialog box.


Email Generator Description

Database "Plus" Email Generator
The following information contains both the description of the Email Generator Utility and instruction on how to use it. Dialog Box screen shots are also supplied to help you understand the functionality of the program.

Note: All the information within this document is also available within the Database Program. Refer to the Help and Information section of the program.

Email Generator Description
This Email Generator utility is a powerful tool. By selecting up to 65,536¹ email addresses from any spreadsheet, you can instantly create fully functional mass distribution direct email letters for use with either Microsoft® Outlook Express or any other email client program. It can also be used to create Comma Separated email address list files for later importing into your email client’s address book.

The image below is a screen shot of a sample file created by the Email Generator Utility and opened Microsoft® Outlook Express. It is addressed to 44 recipients. By using this utility, you can automatically create this type of email letter addressed up 65,536¹ recipients.

Instructions on using of the Email Generator Utility
To launch the Email Generator utility, press the “Utilities” button on the Custom Button Bar menu. You will then be presented with the Database Utilities dialog box as shown below.

Run the Email Generator
Press the “Run Email Generator” button to launch the Email Generator utility. You will then be presented with the Email Generator dialog box as shown below.

Step 1:  What would you like to do ?
Select option 1 to generate a Microsoft® Outlook Express email letter file or option 2 to generate a Comma Separated email address list text file.

If you are not using Microsoft® Outlook Express as your email client program, but are using a different email client program, then choose the second option. This utility will then generate an ASCII text file containing the all email addresses in comma separated format. For example, address1, address2, address3, etc… This format is similar to Excel’s comma separated values (CSV) format. You can then open the generated file any text editor such as Window's Notepad and cut & paste the email address list into your email letter's To:, CC:, or BCC: address locations.

This second option is also useful for exporting the email addresses to Comma Separated list files which can later be imported into your email client’s address book.

Step 2:  Select email addresses from the current database or from any spreadsheet
Choose to either have the program automatically select the email addresses from the current database’s email address field or to manually select them from within any spreadsheet.

If you choose the manual selection option, you will be prompted to select a contiguous range of cells. You can select cells from any spreadsheet, from within either the current workbook or any other workbook. The selection can also be within a column or within a row.

Caution: Depending on the power of your computer, the more email addresses you put into the email letter, the longer it may take to open your email client program.

Step 3:  Sorting of email addresses
Check this box to have the exported email addresses sorted in alphabetical order. You should also check this box if the selected range of cells may possibly contain blank cells. This sorting will not affect your original data.

Compose the Microsoft® Outlook Express Email Letter
If you selected to generate a Microsoft Outlook Express email letter file, you will be presented with a dialog box to compose your letter. Only the checked boxes will be exported.

First, enter your email address in the To: location. By placing your email address here, you can verify that the email was sent properly because you will also receive a copy of the letter.

Select the address location to where you want to place the email addresses:
For effective personalized emailing, it is best to put the list of email addresses into the Bcc: address location. It is not necessary to use the Cc: address location unless you want everyone who receives the email to see the email addresses of the other people who were mailed the same letter. If you select the Bcc: option, the To: email address will not be displayed to the recipients of the letter.

Subject Line, Message Body, and Signature locations:
At this time, you can enter a maximum of 254 characters each for both the Subject Line and Message Body. After the email letter file is generated, you can open it in Microsoft® Outlook Express to edit the text if needed.

Saving the Generated File
At the end of the file generation routine you will be prompted to choose both a directory location and a file name. For the Microsoft® Outlook Express option, the file format is filename.eml. For the Comma Separated ASCII text file option, the file format is filename.txt.

Use the Quick Jump feature to immediately transfer you to the displayed directory path that you use most often. To set the Quick Jump directory path, either type in or cut & paste a directory path into the path edit box, and then press the “Save Path” button.

Emailing the Generated Files
Microsoft® Outlook Express Email Files
To open and email the multi-addressed email letter file in Microsoft® Outlook Express (filename.eml), go to the location where the file was saved and simply double click on it. You can then press Outlook Express’s Send button if you want to immediately send it without further editing.

The image below is a screen shot of a sample file created by the Email Generator Utility and opened Microsoft® Outlook Express. It is addressed to 44 recipients. By using this utility, you can automatically create this type of email letter addressed up 65,536¹ recipients. You also may notice that the text shown in the sample email is the default text shown in the ”Compose an Email Letter” dialog box image above.

Note: It may take some time, possibly a few minutes, to open the email files in Microsoft® Outlook Express if the email address lists contain over 1,000 email addresses. Be patience! Once a file is opened, you can further edit it if needed, or it can be sent immediately.

Comma Separated Email Address ASCII Text List Files
To open the email address ASCII text file (filename.txt) in your text editor, go to the location where the file was saved and simply double click on it. If you are using a different email client program other than Microsoft® Outlook Express, just cut & paste the comma separated email address list into your email letter's To:, CC:, or BCC: address locations.

To import the email addresses from within ASCII text file (filename.txt) into your address book, open your email client program and use its file import utility. Launch the utility and then import the file from the location where the file was saved. Depending on which email client program you are using, it may require a file extension of csv, meaning comma separated value format (filename.csv). Simply, just rename the ASCII text file extension from txt to csv.

Unregistered Version - Demonstration Mode
The shareware version of this program is limited to exporting up to a maximum of 5 real email addresses. However, there is no limit if you use the test email address extension ".cam". The sample email addresses initially provided with this program all have the extension ".cam".

If you purchase and register this program, it will continue to function normally without the 5 email address limit !

Screen Shots
Database Sheet

This example is for a 12 column database (shown with the graphic header on)

Dynamic Charting Capabilities

Period Reports
Automatically generates an included Chart (shown with collapsed Subtotals)


Revision History

Revision Changes: What's different in versions 8.1 through 8.3:

       8.1. A fix for the Subtotal Utility for when data contained the word "total".
       8.2. A fix that only affected blank database sheets.
       8.3. Dialog box display fix that affects Excel 2003 on Windows XP

     That's it.

What's new in Version 8.x
There were many added improvements to this version of the program. Some of the key revision items are listed as follows:

  • Added a separate Setup routine that allows the user to manage and maintain their databases.
  • Added the capability to either create new "Date Enabled Databases" or convert existing databases to a "Date Enabled Databases". A Date Enabled Database works in conjunction with the new Period Reporting feature so that the user can generate weekly, monthly, quarterly, biannual, and annual period reports.
  • Added the capability to automatically perform Background Error Checking. This optional routine will check for and clear all nonblank cells that appear to be blank but actually contain spaces that are not visible to the eye.
  • Added the option to allow the user to display up to six (6) different color Graphic Headers on a Database Sheet. Can be useful for color-coding individual databases when using more than one database in a workbook.
  • Added new Style features which allows the user to do the following:
    • Change the format style of the active database by utilizing built-in Style templates.
    • Quickly to change the color attributes of the active database through an easy to use dialog box menu interface.
  • Added a new Reports feature to the program. This new Reports feature allows the user to automatically generate weekly, monthly, quarterly, biannual, and annual period reports including charts that summarize the results.
  • Added the following new charting features to the DynaChart Function.
    • You can now automatically plot up to four (4) series plots using the DynaChart dialog box interface. You can however, manually add additional series plots to the chart.
    • The user now has the option to automatically add a secondary axis to the charts in addition to the default primary axis.
    • The user now has the capability to dynamically change both the beginning and end points of the plot ranges within the charts. The user will no longer be limited to just plotting the entire database field columns. This is a valuable new feature such that it allows the user to plot a bracketed set of data within the database fields. The resulting chart will then only display the user selected bracketed range of data.
  • Provides the capability to place a Company Name banner onto the database header. Refer to the Registration and Setup information in the Help / Information section.
  • Replaced the Sum function with a Mathematical Summary utility function. This Mathematical Summary utility will allow the user to instantly generate a mathematical summary of any selected range of cells from either the database or any other sheet. The Mathematical Summary will display both basic math and statistical results.
  • Completely overhauled the Help / Information system within the program

What's new in Version 7.0

  • Added a utility feature that allows the user to increase the processing speed of this program if used in Excel versions 8.0 (Excel 97), 9.0 (Excel 2000), or greater.
  • Added a print function to the Information / Help dialog box. Now the user can print out the all the program descriptions and help instructions.

What's new in Version 6.0

  • Supports multiple databases, in which each individual database can include up to 30 columns. Version 5.0 supported databases which could only include up to 20 columns.
  • Added a utility routine that allows the user to convert the cell references of the formulas within the selected range of cells to any form of absolute reference.

What's new in Version 5.0

  • Completely overhauled and optimized the programming code.
  • Increased the auto-intelligence and auto-sensing capability of the program in the terms of user predictability and error checking.
  • Added a variety of alert messages and suggestions
  • Integrated Dynamic Charting capabilities into the program (Allows the user to graphically view the data at any moment.
  • Redesigned the Subtotal Subroutine program giving the user more options for performing a database analysis. Also included the optional statistical functions in the routine.
  • Redesigned the Duplicates Subroutine program to include an option for deletion of the duplicate entries. Also added two more search criteria options to the routine.
  • Redesigned the Sorting Subroutine program to include up to three levels of sort criteria.
  • Redesigned the program buttons on the database sheet(s) (The user can also display or remove them from the sheets)
  • Added additional import features to the Data Import Routine
  • Added more database utility functions
  • Added a graphic database header option

¹ The first 5 rows of a database sheet are used as a header which includes the database field names. For Excel 97, 2000, and 2002's, the maximum amount of data rows per individual database sheet is 65,531. Excel 95's limit is 16,379. This is a Microsoft® Excel limit.

² This 65,536 value is Excel 97, 2000, and 2002's maximum row limit. Excel 95's limit is 16,384.