The information in this article applies to:
- 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:
- In SAMPLE2.XLS, choose Define Name from the Formula menu. In the Name
box, type "Database" (without the quotation marks).
- In the Refers To box, type "SAMPLE1.XLS!$A$5:$E$20" (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
"SAMPLE1.XLS!Database" (without the quotation marks). Choose Close.
- 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:
- In SAMPLE2.XLS, Filter from the Data menu, and then choose Advanced
Filter.
- Under Action, select the Copy To Another Location option.
- In the List Range box, type "SAMPLE1.XLS!$A$5:$E$20" (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
"SAMPLE1.XLS!Database" (without the quotation marks).
- 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.
|