Re: newbie struggling with advanced filter

[Oliver Burnett-Hall <olly burnett-hall co uk>]

Haines Brown wrote:
> I have a worksheet that has data A2:N1178, and I want to filter it to
> create a new worksheet that has select columns from the original as
> well as select rows based on a filter.
>
> My first question is whether I necessarily must use an advanced
> filter. The autofilter seems to have a maximum capacity of 150
> rows, which is far too small for my purpose. Is this so?

What version of Gnumeric are you using?  I'm running 1.6.1 and there 
isn't this limitation.

> So I use the Advanced Filter. In preparation, I type in a filter
> criterion in cells I 1179 to I 1180. The first item is just a copy of
> the column label; the second is a simple filter of the data in the I
> column: 
>
>           exp.year
>           >2005
>
> In the Advanced Filter, I put:
>
>    List range:     $A$2:$I$1178
>    Criteria range: $I$1179:$II1180
>
> This just gives me: "The Given Criteria are Invalid". So my question
> is, why?

I get that error if the column heading in the criteria field(s) don't 
match columns in the data fields.  So I suggest you've check that you've 
typed 'exp.year' correctly and it matches one of the other columns.

> It seems odd to include a copy of the column label again here. Is that
> required, or is the gnumeric Manual just elaborating an example? 
>
> Can the criteria range refer to a single cell, such as $I$1180?

The column name is definitely needed - your data table has nine fields, 
so how is gnumeric supposed to know which field the criteria applies to 
without telling it the field name?

> Is there a simpler or more intuitive way to feed to the Advanced
> Filter a filter criterion?

The advanced filter stuff is fairly close to the way it's done in Excel. 
 I agree that it isn't very intuitive, but I can't think of a better 
method.

> I assume the List Range need not include entire spreadsheet.

No, you can set it to any range.

HTH,

- olly