Advanced Filter

[Alex]

In using office 2003 and playing with advanced filter. When I run it I am
getting :
'The extract range has a missing or illegal field name' error message.

All the columns have field names - cant find anything on MS other than for
Excel 97/98 and missing column headings

Any help much appreciated

A

 

[Dave F]

Do you have any hidden columns? Any column headings with unusual characters?

Dave

 

[Alex]

Hi Dave

No nothing, its just a test worksheet with the following columns and 10/12
rows of data
Surname, Forename, Title, Telephone, Address1, Town, PostCode, AnnualTrips,
Income

Alex

 

[bj]

do you have the field names exactly repeated in the criteria section

check with
=field name = criteria field name
if answer is false you would get this type of error.

 

[Dave Peterson]

Insert|Name|Define
and delete the name "Extract"

Then try again.

 

[Johnny]

bj

yes I have recreated and did a straightforward Copy.

If I run an Advanced Filter > Filter the List in Place, it works Ok, Its
when I do an Advanced Filter > Copy to Another Location ( which is on the
same sheet) that the problem occurs.

I am not sure what you want me to check - sorry . As you can tell I am just
getting to grips with Excel

A

 

[Johnny]

When I go Insert>name>Define, the only one defined is Criteria

A

 

[bj]

when you are selecting your copy to range are yuou selecting one cell or
several cells.
I can get your message if I select several cells, but do not get it if I
select just the upper left cell of the range I want the data to go to.

 

[Johnny]

Hi
No, in the Copy Range I am just entering a single cell reference J4. If it
helps, the column titles are being copied, but the filtered data isnot

Apologies for change of user name - I am on a colleagues computer
A

 

[Johnny]

Eureka

If I tell Advanced filter to copy to J4 ( which is alongside existing data,
I just get the column titles.
If I tell Advanced Filter to copy to say A19 (below the existing data) it
works perfectly.

Why is that?

Thanks
A

 

[bj]

no clue, If I select any cell outside the filtered cells It works for me, I
have 2003 excel

 

[Dave Peterson]

If you delete that, what happens?

When I go Insert>name>Define, the only one defined is Criteria

A

 

[Johnny]

Hi Dave

No joy, still the same. It seems that if I try and copy to the adjacent
column (in this case J) to the existing list, i get the error message,
although the column names are copies, just no filtered data. However if I
set the Copy to say column K, then it works OK

A

 

[Dave Peterson]

Maybe you could describe exactly what you're doing.

Do you select your range before you start Data|Filter|advanced filter? If
you're not, maybe excel is not guessing your range correctly.

The extract name could be hidden (it wasn't for me, though).

Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

If you see that name, delete it and try again.

 

[Johnny]

Hi Thanks for your continued help
Ok I have a workseet called Sheet 1.
Row 1 Columns A to I inclusive had column names.
Row 2 is blank
Row 3 Column A to I include has the same column names as in Row 1
Rows 4 to 13 inclusive contain test data.

A4 to I13 is a list (I'm using Office 2003)

In G2 I place the text string on which I want to filter
I select the List at G4
I select Data > Filter > Advanced Filter
The dialog box appears. I select Copy to another location radio button.
In the Copy to box:
If I type (or use the selector, or click in the cell) Sheet1!J4 or $j$4 or
J4, and press the OK button. I get the column names appearing in row 4 and
the error message as mentioned.
If I select Copy to A15 or J3 or J5 or K4 the filter works perfectly.

I am totally befuddled as to why it will not work if I select J4 as the copy
to cell

Alex

 

[Dave Peterson]

I think it's the List that's causing the trouble.

The list likes to expand horizontally and vertically when you add data to it
(you can see the list border expand).

When I removed the list, I didn't have any trouble.

So I think the choices are remove the list or move over to a non-adjacent
column.

ps.

You wrote this:

I select the List at G4

I like to select the range (G4:g13) to start. Then I don't have to change the
range in the dialog.