Sorry, the amended formula should, of course, be

=OFFSET(Data!$A$9,0,0,COUNTA(Data!$A$9:$A$65536),2 2)

--
Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
Hi Ken

Your problem is that your Database range does not include a Header row.
So Advanced filter, doesn't know what it is looking for.
Change from
=OFFSET(Data!$A$10,0,0,COUNTA(Data!$A$10:$A$65536) ,22)
to
=OFFSET(Data!$A$9,0,0,COUNTA(Data!$A$10:$A$65536), 22)

By the way, if you select say row 25 on sheets dataControl+Shift+arrow
down to select all rows to the endright clickDelete and then save the
file it will shrink to about 110 KB

--
Regards
Roger Govier

"ken" wrote in message
...
G'day there Bob,

You can find it he

http://people.aapt.net.au/~AAPT_14660421/

It's not a fancy page, just a blank white expanse with a link to the XL
file but it worked here a minute ago. I should warn you though, that
it's over 2 Megs. I have no idea why, but I think it might be because I
formatted a couple of columns down to the bottom of the sheet since I
dunno how many entries there can be.

Thanks for helping, it's much appreciated.
Ken

Bob Phillips wrote:

Ken,

It is a bit hard to debug without the data (needless to say, a simple
test
works fine here). Can you post the workbook somewhere on a free
fileshare
site?

HTH

Bob

"ken" wrote in message
...
G'day there One & All,

After much swearing, cursing & experiment - all ineffective - I'm now
asking for assistance with my latest fiasco.

I have a list of data which includes dates, text, and numbers but none
combined, i.e. each column has only one of these types. There are 22
columns, but I need only about 12 as criteria. I've placed these above
the data list with a couple of rows separating them.

I have the data in a dynamic range labelled "Database", and the critera
in a static range which I've named "Criteria". As you can see, I have a
gift with inspired name assignments :)

To ensure the criteria names are identical to the data column titles I
have now applied a formula to each. These are simply "=A10" or
whatever.
I initially copied & pasted the headings, but had to rename a few so I
did this in case I need to rename them again. Regardless, my filtering
didn't work in either case.

I have generated about 4 rows of dummy data of the same form that the
real data will be. I then copied these 3 times to give 12 rows of data.

The person's names are of the format 'SMITH A B'. Because of the
duplication there are 3 records with that field entry. I have used
'SMITH' in the appropriate criteria column and then tried filtering. I
found that this returned every record. Even those whose name is 'BROWN'
or whatever. I have tried copying to another location and filtering in
place, but with the same result each time. I've tried 'SMITH*';
'="=SMITH"'; '="=SMITH*"'; all with the same result - all records
returned. This was when I used the entire criteria range of 11 columns
with 3 rows, but only one criteria entered.

I then used only that criteria. That is, I used only the two cells
holding the heading & 'SMITH' as my criteria. This made a change, in
that it returned the first record only. It did have 'SMITH' as the
name,
but the other records with this name were not returned.

I have experimented with different date formats, and returned either
all records or none. Even copy/pasting the date entry from the data to
the criteria hasn't worked.

I like to blame Microsoft for making faulty software, but I'm
inclined to think that I've mucked this up by myself. I've searched the
'net but not found anything to assist.

If anyone here has any ideas, please point them at me :D

Thanks for listening,
Ken McLennan
Qld, Australia


__________ Information from ESET Smart Security, version of virus
signature database 4760 (20100111) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus
signature database 4760 (20100111) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4760 (20100111) __________

The message was checked by ESET Smart Security.

http://www.eset.com