I think I did everything you said. I get an error that there is a missing
field or invalid name in my extract Name.
What sheet and what cells do you have highlighted when you select Adv Filter?
:
Here's an approach to try:
Sample Assumptions:
Sheet1 contains your data in cells A1
![Big Grin :D :D](/styles/default/custom/smilies/grin.gif)
100 with the following column titles
A1: MyCol_1
B1: MyCol_2
C1: MyRefCol.....(This one contains the data you want to filter on)
D1: MyLastCol
Sheet2 is where you want the extracted data to be displayed
Using Sheet2 (contains your column headings from Sheet1):
A1: MyCol_1
B1: MyCol_2
C1: MyRefCol
D1: MyLastCol
Insert>Name>Define
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$D$1
I1: MyRefCol
I2: 1
Insert>Name>Define
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2
Still using Sheet2:
Insert>Name>Define
Names in workbook: Sheet2!Database
Refers to: =Sheet1!$A$1:$D$100
(Notice: you are on Sheet2, and creating a Sheet2-level range name, but
the referenced range is on Sheet1)
The reason: An advanced filter cannot SEND data to another sheet, but
it can PULL data from another sheet.
Now...set up the Advanced Data Filter:
<Data><Filter><Advanced Filter>
Select: Copy to another location
List Range: (press F3 and select Database)
Criteria Range: (press F3 and select Criteria)
Copy To: (press F3 and select Extract)
Click [OK]
Note: if you want to run that Advanced Data Filter repeatedly,
you'll need to re-select Database each time
Is that something you can work with?
***********
Regards,
Ron
XL2002, WinXP
:
In one worksheet, I have a set of about 15 columns. For those rows that have
a value of 1 in column 3, I want to copy those to another worksheet. Both
worksheets are in the same workbook.
My current # of rows is only 170. I want sheet #2 to automatically grow as
things are added to sheet 1.
How?