Display autoFilter criteria

[MrGadget]

Ok here's my problem:
I have a column of Store Names ie. Wal-Mart, Target, etc.
When I apply the AutoFilter to display all Wal-Marts for example, I want a cell, we'll call it Cell:B4 on a different worksheet within the same workbook to display the name of the store selected in the AutoFilter (Wal-Mart in this example). I need that same cell to change anytime the AutoFilter is changed to another store.

So, Cell:B4 should always display what the filter is set to ie. All; Wal-Mart; Target; or whatever store I choose.

Can anyone help?

Thanks in advance

[NBVC]

So if your filter row is in Row 1, and your store name column is in column, then your formula in the other sheet could be as easy as: =Sheet1!A2 where Sheet1 is the name of the sheet with the filtering and A2 would be the first cell containing the filtered store name....

[MrGadget]

So if your filter row is in Row 1, and your store name column is in column, then your formula in the other sheet could be as easy as: =Sheet1!A2 where Sheet1 is the name of the sheet with the filtering and A2 would be the first cell containing the filtered store name....

That's where my problem is. When I apply the filter to the column with the store names, the row number isn't static. So if I reference A2, my result never changes. If I set my filter to show all Wal-Mart Stores, the first displayed row now changes to A335.

If it helps at all I've attached a Demo Spreadsheet. Check out both sheets in the workbook. Try filtering by various store names and check out the result on sheet2.

Thanks for your help.

[VBA Noob]

See if this helps

Code from this site

http://www.j-walk.com/ss/excel/usertips/tip044.htm

or Non VBA way instead of using filter use advance filter and reference that cell

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

VBA Noob

[corinereyes]

Hi MrGadget,

Try the attached file. Made a little revision with the code.

http://www.ozgrid.com/VBA/autofilter-criteria.htm

VBA Nood,

I'am using xl 2007 and the xl file you've attached is not working when i make a new filter (does not refresh). Correct me if im wrong. Thanks!

[VBA Noob]

Is the same code as my link except someone has added the line

Application.Volatile

to force updates

VBA Noob

[Sinclair]

In Excel 2007 you can filter on multiple values rather than just two. However, VBA only has a Criteria2 property for a filter -- so you can't display more than two filter criterias even if you picked, for example, "1 OR 2 OR 3," etc.

Is there any way to get this data in Excel 2007? The tool tip that you see when hovering over the autofilter arrow displays just what I need, so there's some way of doing this, though it may not be accessible via VBA.


I really appreciate anyone's help. I filter many columns at the same time all day long at work and being able to quickly see the parameters would help enormously.

[NBVC]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

[ptwuk]

How can I display the results without the =

For example the output in Book1 displays =WAL-MART in the cell on shhet 2

How can I get it to just display WAL-MART

[Domski]

You might want to read NBVC's post before yours

Dom

[NBVC]

To make it official....

ptwuk,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.