Prev: Regular Workbooks vs. Binary Workbooks
Next: HOW DO YOU ALPHABETIZE A LIST OF NAMES IN AN EXCELL SPREADSHEET?
From: T. Valko on 27 Apr 2010 20:52
>All those contains, greater than, does not...

If that's what they want, the filter criteria, then I got nothin'!

Here's what the formula I suggested will do...

D1 = column header = Car Make
D2 = Chevy
D3 = Ford
D4 = Ford
D5 = Chevy
D6 = Chrysler

If you filter that column on Car Make>Chrysler then the formula will return
Chrysler.


--
Biff
Microsoft Excel MVP


"Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
news:4BD782BE.F4BC156B(a)verizonXSPAM.net...
> Unless the user is showing blank cells and those cells are really empty
> <vbg>.
>
> Seriously, Biff's formula won't show you the criteria unless it's pretty
> simple. All those contains, greater than, does not... would return a
> value, but
> maybe not what you're looking for.
>
>
>
> "T. Valko" wrote:
>>
>> If they mean to return the first item from a filtered list...
>>
>> Array entered** :
>>
>> =INDEX(D2:D15,MATCH(1,SUBTOTAL(3,OFFSET(D2:D15,,,ROW(D2:D15)-ROW(D2)+1)),0))
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
>> SHIFT
>> key then hit ENTER.
>>
>> That will return the first item from the filtered (or unfiltered) range
>> D2:D15.
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
>> news:4BD77A82.29A8A160(a)verizonXSPAM.net...
>> > Nope. You'll need VBA.
>> >
>> > LP wrote:
>> >>
>> >> Without going into VB, is there a way to display the chosen autofilter
>> >> value?
>> >>
>> >> Column D1 is where I want to filter is located and it has 200 rows.
>> >> Thanks.
>> >
>> > --
>> >
>> > Dave Peterson
>
> --
>
> Dave Peterson


From: Dave Peterson on 27 Apr 2010 21:44
Sometimes, when I was working with data I wasn't sure about was to add a column
of 1's and label it Counter.

Then each row of data had at least one field that was always there--no matter
how I filtered the data.

And it did make it easier to count the rows that were visible after the filter
was applied.

I know my co-workers didn't appreciate that extra column...until they actually
used it!



"T. Valko" wrote:
>
> >All those contains, greater than, does not...
>
> If that's what they want, the filter criteria, then I got nothin'!
>
> Here's what the formula I suggested will do...
>
> D1 = column header = Car Make
> D2 = Chevy
> D3 = Ford
> D4 = Ford
> D5 = Chevy
> D6 = Chrysler
>
> If you filter that column on Car Make>Chrysler then the formula will return
> Chrysler.
>
> --
> Biff
> Microsoft Excel MVP
>
> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
> news:4BD782BE.F4BC156B(a)verizonXSPAM.net...
> > Unless the user is showing blank cells and those cells are really empty
> > <vbg>.
> >
> > Seriously, Biff's formula won't show you the criteria unless it's pretty
> > simple. All those contains, greater than, does not... would return a
> > value, but
> > maybe not what you're looking for.
> >
> >
> >
> > "T. Valko" wrote:
> >>
> >> If they mean to return the first item from a filtered list...
> >>
> >> Array entered** :
> >>
> >> =INDEX(D2:D15,MATCH(1,SUBTOTAL(3,OFFSET(D2:D15,,,ROW(D2:D15)-ROW(D2)+1)),0))
> >>
> >> ** array formulas need to be entered using the key combination of
> >> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
> >> SHIFT
> >> key then hit ENTER.
> >>
> >> That will return the first item from the filtered (or unfiltered) range
> >> D2:D15.
> >>
> >> --
> >> Biff
> >> Microsoft Excel MVP
> >>
> >> "Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
> >> news:4BD77A82.29A8A160(a)verizonXSPAM.net...
> >> > Nope. You'll need VBA.
> >> >
> >> > LP wrote:
> >> >>
> >> >> Without going into VB, is there a way to display the chosen autofilter
> >> >> value?
> >> >>
> >> >> Column D1 is where I want to filter is located and it has 200 rows.
> >> >> Thanks.
> >> >
> >> > --
> >> >
> >> > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
First  |  Prev  | 
Pages: 1 2
Prev: Regular Workbooks vs. Binary Workbooks
Next: HOW DO YOU ALPHABETIZE A LIST OF NAMES IN AN EXCELL SPREADSHEET?