Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
self-sizing adv.filter criteria range
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum a range after multiple criteria | Excel Discussion (Misc queries) | |||
sumif when criteria is a range | Excel Discussion (Misc queries) | |||
use a date range as criteria in a countif formula | Excel Worksheet Functions | |||
Specify a null value in an Excel Database criteria range | Excel Worksheet Functions | |||
can I use a range of dates as a criteria when using sumif? | Excel Worksheet Functions |