Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: External Data Validation.
The data validation feature of Excel is quite handy. You can use the feature to specify a range of values that are considered acceptable for user input. Normally, Excel expects you to specify your validation range as being on the same worksheet where you are defining the validation rule. If you try to enter a range that is on another worksheet or in another workbook, Excel balks and gives you an error message.
What if you want the validation range to be on another worksheet, just so you don't clutter up the current worksheet with extraneous data? The easiest way to do that is to follow these general steps:
This approach works great if the data validation range is in the same workbook. What if you want to use a data validation range that is in a different workbook entirely? You can trick Excel into accepting your external reference if, in step 7, you enter a formula such as the following:
This formula uses the INDIRECT function to return the value at a cell on another worksheet, and the data validation feature will accept it with no problems. In this case the cell being checked is at cell D6 on Sheet1 of Book2. In order for this to work, you will need to make sure that Book2 is open at the same time that your main workbook is open.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2813) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: External Data Validation.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!