self-sizing adv.filter criteria range

[Guest]

I'd like to have an advanced filter which does not need to be told, manually,
how high the criteria range needs to be for a filter. In other words, I want
a user to be able to enter either one line of data, or two lines, or maybe
more. If I put an adv.filt on a range for three lines at once, then if I
don't have and OR criteria (on two lines), the third line ends up serving as
an "all" criteria, and the first line of entry is useless. For example:

A criteria B criteria

2 (blank)

(blank) (blank)

If I highlight the entire 6 cell range for criteria (in anticipation that on
one of the reuses of the filter, B2 will have something in it, or A2 even),
then the filter will return all records, since it's got the OR of a blank.
However, if I select just the four cells of the names, and the first line of
criteria, then when I resuse adv.filter, I will have to manually stretch the
range to include the second line of criteria, in the case that I have
something like:

A criteria B criteria

2 (blank)

(blank) "test"

where I want to include >2 A criteria OR "test" B criteria.

So what I am thinking is that I can have defined name like "CriteriaRange",
which does some sort of offset of the "A criteria" cell, with width/height
that senses the end points of my range. The other option, I suppose, is to
have a cell off to the side, which uses text to create the range, but again,
sensing somehow the upper left and lower right end points of the range.

So the questions are 1) am I on the right track, 2) is there a simpler way,
3) how would I get this auto-sensing range, if that's the way to go?

Thanks much. Sorry for length of explanation.
, criteria range which dynamically figures out how many lines of criteria
to read. The problem I have is that I want to use advanced filter

 

[Dave Peterson]

This seemed pretty straight-forward to me and it seemed to work:

I created a new sheet (Sheet3) in my sample:

I put this in A1
=IF(COUNTA(B1:IV1)=0,"",ROW())
and dragged down as many rows as I thought I'd ever need--then a few more rows.

Then I would build my advanced filter range in B1:x### of Sheet3.

Then I used Insert|Name|Define and created a name:

Names in Workbook: myFilterRng
Refers to: =offset(sheet3!$b$1,0,0,max(sheet3!$a:$a),counta(sheet3!$1:$1)-1)

And I could go back to my data sheet and use:
data|filter|advanced filter
and specify
myFilterRng
in the criteria range box.

And it worked!

=======

Debra Dalgleish has some more notes about these dynamic range names:
http://contextures.com/xlNames01.html#Dynamic

=======
Could that formula in column A be embedded into the "refers to" formula?

Maybe. I'll leave it to you to try.

 

[Guest]

what is x###? That's one thing that's throwing me. Thanks.

 

[Dave Peterson]

I have no idea how wide your table should be. So I used Column X. And since I
didn't know how many rows, I used ###.

B1:Whateveryouneed
????

what is x###? That's one thing that's throwing me. Thanks.