View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Coderre Ron Coderre is offline
external usenet poster
 
Posts: 2,118
Default How to copy rows that meet criteria to another sheet in Excel

The column titles in the Extract range must match the column titles in the
Database range. You don't have to use all of the titles and they don't have
to be in the same order...but, they must still match.

This would trigger the error you mentioned:
If there is no "Column_Total" heading in the Database, then you wouldn't be
able to pull it into the Extract range.

Does that help?

***********
Regards,
Ron

XL2002, WinXP


"Bruce" wrote:

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?