>
> what is x###? That's one thing that's throwing me. Thanks.
> --
> Boris
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > BorisS wrote:
> > >
> > > 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
> > > --
> > > Boris
> >
> > --
> >
> > Dave Peterson
> >