Knowing what you have Autofiltered

[Alejo]

Hello Guys,

I have a very large table with data from different products around SouthAmerica. Whenever I want to make a certain extraction I use Autofilter which is so far the best way to (in a way) Query the Table, without getting involved with much code. This is cool when filtering a daily query of 3-6 items. However when it comes to more is like you have to write down the fields filtered in order to know what has been queried. Is there a way to have show in a cell what has been selected in AutoFilter?

 

[Tom Urtis]

One of the example variations in this posting might help:
http://www.mrexcel.com/board2/viewtopic.php?t=158947

 

[Alejo]

I get the #Name? error

Sorry but that error is shown on the A1 ...N1 range, where =FilterCriteria(A9) was pasted...

The code is on the "This Worksheet" page in VB, I have also tried pasting it into the Sheet1...

Any clues?

 

[Greg Truby]

Alejo,

You might have a look and see if this will work for you.

Saludos,

 

[Alejo]

Sorry Amigo

But I have done this (what I´ll write you) and the error remains:

Created new sheet
Pasted on cell A3 the data
Applied AutoFilter
Copied your code
Returned to sheet, view code, paste on Empty module
Copied the =myNewUDF() on A1 and along the fields that are with data & filter

...and #Name? comes up!

What am I doing wrong?

 

[Nimrod]

Hello Alejo

Instead of using AutoFilter use "Advanced Filter" ... Here's a great page to read up on this...

http://www.contextures.com/xladvfilter01.html

 

[Greg Truby]

Alejo,

Sorry, the instructions I posted there are generic instructions for creating a UDF in general. The UDF reference in the cell must match name of the function in VBA. So in this case the cell needs to have =udfAFCriteria() as the formula. Sorry for the confusion.

Perdón pero las instrucciones que puse en esa otra discusión eran generales para como crear una función individual. La referencía en al celda tiene que utilizar el nombre de la función que uno puso en VBA. O sea en este caso la fórmula tendría que ser =udfAFCriteria() Favor disculpe la confusión.

Oh, and Nimrod has a point in that Advanced search is a bit more "self documenting". I like Autofilter since I always have a hard time getting the criteria correct with Adv. Filter. For me, the main strength of Advanced Search is that you can "OR" between columns whereas with AutoFilter it's always "AND" between columns.