View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bruce Bruce is offline
external usenet poster
 
Posts: 138
Default How to copy rows that meet criteria to another sheet in Excel

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?

"Ron Coderre" wrote:

Here's an approach to try:

Sample Assumptions:
Sheet1 contains your data in cells A1:D100 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

InsertNameDefine
Names in workbook: Sheet2!Extract
Refers to: =Sheet2!$A$1:$D$1

I1: MyRefCol
I2: 1

InsertNameDefine
Names in workbook: Sheet2!Criteria
Refers to: =Sheet2!$I$1:$I$2

Still using Sheet2:
InsertNameDefine
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


"Bruce" wrote:

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?