Microsoft KB Archive/179859

= XL98: You cannot use external references with Data Validation =

Article ID: 179859

Article Last Modified on 1/10/2007

-

APPLIES TO


 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q179859



SYMPTOMS
When you type a cell reference to a cell in the Source box or in the Formula box in the Data Validation dialog box, and then click OK, you receive the following error message:

You may not use references to other worksheets or workbooks for Data Validation criteria.



CAUSE
This problem occurs if the following conditions are true:
 * You select the cells to which you want to apply data validation, and then click Validation on the Data menu.
 * You click the Settings tab, and then click Settings (or click Custom) in the Allow list.
 * You specify a cell reference to a cell in another worksheet or workbook in the Source (or Formula) box.

The Validation command lets you put restrictions on data that is typed into specific cells. However, the cells that contain the data criteria can refer only to cells within the same worksheet as the cells that are restricted.



Use a local cell that refers to the external cell
 On the File menu, click New, click Workbook, and then click OK. Select cell A1. On the Data menu, click Validation, and then click the Settings tab. In the Allow box, click Whole number. In the Data box, click Equal To. In the Value box, type a reference to a cell on the worksheet, for example, type =$B$1 .</li> Click OK.</li> In the cell that you referenced in step 6, type a formula that refers to the external criteria cell. For example, in cell B1, type the following formula:

=Sheet2!$C$1

</li> In the external cell, type the criteria value that you want to use for data validation. For example, in cell C1 of Sheet2, type the number 5 .</li></ol>

You can type only the data validation criteria (for example 5 ) in cell A1 of Sheet1. You can type only the data validation criteria (for example 10 ) in cell A1 of Sheet1.

<div class="moreinformation_section">

MORE INFORMATION
Microsoft Excel 98 Macintosh Edition lets you specify what data is valid for individual cells or for cell ranges in a worksheet. This is referred to as data validation. To use data validation, click Validation on the Data menu.

Restrictions include values, dates, times, or lists of text or values, and can be limited to exact matches or ranges of cells. You can type the valid values in the Data Validation dialog box, or you can store them in worksheet cells. These validating cells must be on the same worksheet as the cells want to restrict.

<div class="references_section">