Getting Started #
Before you Import or Update records you should always use the Format Table button. That button adds missing default values and formats the value of every cell to match the formats in your database.
Options #
The Format Table button has the following options which are individually controlled under the Options button on the ribbon.
- Apply Conditional Formats
- If you want conditional formats applied to the sheet leave this checked.
- Add Default Values
- Field Default values will be applied to empty cells only.
- Y/N will default for check boxes.
- 0.00 will default for most numeric fields.
- Add Missing Co/Group Values
- Company/Group values will be applied to empty cells only. (see Replace Co/Groups Button below)
- These values are controlled by the Destination Company field under the Options button on the ribbon.
- Change Data Format
- This applies your Code Formats to the Job, Contract, Phase, GL Accounts, Equipment, etc. fields.
The default Options profile has Apply Conditional Formats as unchecked while the other three options are checked.
Conditional Formats #
The Conditional Format option is useful for identifying data errors prior to importing. The available Conditional Formats are comprised of the following.
- Required Cells
- If a required cell is missing a value it will be red.
- Max Length Exceeded
- If a cell’s field length is exceeded it will be green.
- Invalid Number or Date
- If a cell has an invalid number or date it will be orange.
- Number Outside Allowable Range
- If a cell’s value is outside the allowable range it will be purple.
- Duplicate Records
- If duplicate records exist in the table they will be highlighted gray.
It is recommended to leave the Apply Conditional Formats option off when working with large data sets as it slows Excel down.
Sub-Menu Buttons #
The Format Table button has a sub-menu consisting of the following buttons.
Replace Co/Groups #
While the Format Table button only applies Company/Group values to empty cells only, the Replace Co/Groups button replaces all existing values.
This button can be used if you are copying records from one company to another.
Click the button and it will replace all Company/Group columns based on the Destination Company option value.
Delete Empty Columns #
Use this button to delete all columns without a single value.
This can be useful to clean up your layout but be careful as you may end up deleting a column you may need. Use the Move Empty/Option Columns button instead as it’s a safer option.
Format Selected Column #
This button can be used to format a single column rather than the entire table. You may have only changed values in one column so this is a faster method.
Select any cell in the column you want to format and click the button.
Move Empty/Optional Columns #
Use this button to move all Empty Columns and Optional Columns to the end.
That should leave the Required Columns and Columns with values at the beginning making some Tables easier to analyze rather than scrolling across.
Copy Identified Rows #
Use this button to copy one or multiple rows. This can be useful if you are importing multiple copies of the same setup rather than copying and pasting the records.
Make sure to assign Copy in the *Action column to the rows you want to copy.
You will receive the prompt below when you click the button.
Auto Size Columns #
This will auto size every column in the table.
Apply Default Row Height #
This will apply the default row height of 15 to every row in the table.
Hide Current Sheet #
This hides the current sheet and toggles the Hidden value on the TAD sheet.
This is an original feature and unlikely to be needed/used anymore.
Add Column Properties Table #
This adds a table showing the column properties for each column.
You will be able to see which columns are Required, the Field Length and Default Values.
Regenerate Excel Formulas #
Whenever you use Format Table and other buttons the Excel formulas will be converted into the value that displays in the cells. If you need to add the formulas back to those columns you can use this button to do that rather than manually entering them in again.