Microsoft KB Archive/28190

-

{| 2.x 3.00 4.00 5.x 7.0 7.0a | 2.20 2.21 3.00 WINDOWS                   | OS/2 kbusage The information in this article applies to:
 * width="100%"|


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.x, 7.0, 7.0a
 * Microsoft Excel for OS/2, versions 2.2, 2.21, and 3.0
 * Microsoft Excel for Macintosh, versions 2.2, 3.0, 4.0, 5.x

In Microsoft Excel, if you have a database set up on a worksheet, you can extract data to a second worksheet using an external reference to the first worksheet for the database or list range.

Microsoft Excel Versions 2.x, 3.0 and 4.0
In the following example, SAMPLE1.XLS has a database defined as $A$5:$E$20. To extract data from SAMPLE1.XLS to SAMPLE2.XLS, do the following:


 * 1) In SAMPLE2.XLS, choose Define Name from the Formula menu. In the Name box, type &quot;Database&quot; (without the quotation marks).
 * 2) In the Refers To box, type &quot;SAMPLE1.XLS!$A$5:$E$20&quot; (without the quotation marks). Or, if you have already defined this range on SAMPLE1.XLS as a database using the Set Database command on the Data menu or using the Define Name command on the Formula menu, you can type &quot;SAMPLE1.XLS!Database&quot; (without the quotation marks). Choose Close.
 * 3) Set and use the Criteria and Extract ranges on SAMPLE2.XLS as you normally would.

Microsoft Excel Version 5.x or 7.0
In the following example, SAMPLE1.XLS has a database defined as $A$5:$E$20. To extract data from SAMPLE1.XLS to SAMPLE2.XLS, do the following:


 * 1) In SAMPLE2.XLS, Filter from the Data menu, and then choose Advanced Filter.
 * 2) Under Action, select the Copy To Another Location option.
 * 3) In the List Range box, type &quot;SAMPLE1.XLS!$A$5:$E$20&quot; (without the quotation marks). Or, if you have already defined this range on SAMPLE1.XLS as a database using the Define Name dialog box, you can type &quot;SAMPLE1.XLS!Database&quot; (without the quotation marks).
 * 4) Enter the Criteria Range and in the Copy To box, enter the range you want to extract the data to.

NOTE: SAMPLE1.XLS must be open when you extract the data. For more information on the advanced filter, search on advanced filter from Excel's on-line help.
 * }

-

Last reviewed: July 14, 1996

© 1998 Microsoft Corporation. All rights reserved. Terms of Use.