XL97: Cannot Use External References with Data Validation

WORKAROUND

Method 1: Using a Local Cell that Refers to the External Cell

1. On the File menu, click New, click Workbook, and then click OK.
2. Select cell A1.
3. On the Data menu, click Validation, and then click the Settings tab.
4. In the Allow list, click Whole Number.
5. In the Data list, click Equal To.
6. In the Value box, type a reference to a cell on the worksheet, for example, type "=$B$1" (without the quotation marks).
7. Click OK.
8. In the cell 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
9. In the external cell, type the criteria value you want to use for Data Validation. For example, in cell C1 of Sheet2, type the number 5.
   
   You may now type only the Data Validation criteria (for example 5) in cell A1 of Sheet1.

Method 2: Using INDIRECT to Return a Value in the External Cell

1. On the File menu, click New, click Workbook, and then click OK.
2. Select cell A1.
3. On the Data menu, click Validation, and then click the Settings tab.
4. In the Allow box, click Whole number.
5. In the Data box, click "Equal to."
6. In the Value box, type the following formula
   
   =Indirect("Sheet2!$C$1")
   
   and then click OK.
7. In cell C1 of Sheet2, type the number 10.
   
   You may now type only the Data Validation criteria (for example 10) in cell A1 of Sheet1.