Share this Page URL

Chapter 26. Error-Proof Data-Entry Techn... > Troubleshooting Data Errors - Pg. 566

Error-Proof Data-Entry Techniques 566 Troubleshooting Data Errors When you audit a worksheet, Excel finds cells that contain values that are outside the limits you define with data validation rules. This technique is the only way to find incorrect values that a user pasted using the Clipboard, or invalid values that are the result of a formula. These auditing tools are not available from any menu. The only way to identify invalid data is to click a button on the Auditing toolbar; curiously, this option never appears on the list of toolbars that Excel displays when you right-click an existing toolbar. To display the Auditing toolbar, choose Tools, Auditing, Show Auditing Toolbar. Note The Circle Errors button will find a maximum of 255 cells. If you have more invalid entries, you'll need to correct the data in some of the invalid cells, and then click the Circle Invalid Data button again. For an overview of other tools you can use to track down problems in formulas, see "Trouble- shooting Formulas." Troubleshooting Converting Numbers to Names You've used the Template Wizard with Data Tracking to link a template and database, but the re- sulting database doesn't contain the data you expect. If you've placed controls on your form, be sure the cell you use to fill your database contains mean- ingful data. A combo box control, for example, produces only an index number in its linked cell; that means your database will contain numbers rather than the names you expect to see. You'll need to use an INDEX formula to look up the text value, and then assign the cell that contains that formula to the database. If one or more columns in your database are blank, you may have selected the wrong cell to link to the database. Run the Template Wizard with Data Tracking again and double-check the links in step 3. Data Validation Limitations You created a set of validation rules to protect data entry, but when users returned the filled-in worksheet, you found invalid data in those cells. You've triple-checked the data validation rules, and you're certain they're working properly. What's the problem? Validation settings apply only when the user types data into a cell. If the user copies or cuts data from another source and pastes it into the cell via the Clipboard, Excel ignores the rule. There is no workaround for this problem, so you'll have to train your users not to use the Clipboard when filling in forms. Also, if any cell contains a formula as well as a data validation rule, Excel ignores the rule. If you want to triple-check the values in cells protected by data-validation rules to make sure they're correct, use the Go To dialog box. Press F5 and click the Special button, and then check the Data Validation option. Click All to see all cells with data validation rules, or Same to see only cells whose rules match the currently selected cell.