×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel Filtering Help
2

Excel Filtering Help

Excel Filtering Help

(OP)
Hi all,
I have a Excel sheet that has a bunch of data in columns in it. I am wanting to create a sort of "header interface" that doesn't need the selecting of the filter button on the columns (which I already have there). Instead I want a field that says something like:

Pick a type:
And next to that there is a dropdown box that I want to populate based on the unique values in that column. (I know if I were doing this in FoxPro, I could just do "Select DISTINCT FROM <Column>", but not sure how to do an equivalent of that in Excel. So once they pick it from the drop down, I will update other dropdowns with what has been removed from their starting point. But I'm not sure how to create a dynamic dropdown list that will get the distinct values form the column.
Then the next problem is, how do I use that cells value to apply to the filter condition?

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."hammer

RE: Excel Filtering Help

Hi,

1. SELECT DISTINCT [<columnNAME>] FROM [<sheetNAME>$]

2. Record a macro where you manually select a single value in AutoFilter. Post your recorded code if you need help automating.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Filtering Help

(OP)
Thanks Skip, so is VBA the only way to accomplish this?

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."hammer

RE: Excel Filtering Help

I can't see how else. But I don't know why you'd want to NOT use the AutoFilter feature AS IS!

You would have a very complex process for sucessive queries based on your heading selections. All that is built into the AutoFilter feature.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Filtering Help

Another thought. Don't know how many sucessive "filter" selections you need to make, but for any dropdown selection, you can link to a sheet that has a PARAMETER query that would be based on your selection and executes whenever the parameter values changes. No VBA required. All querytable ranges can be accessed via Named Ranges or ListObject Structured Table ranges.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Filtering Help

(OP)
Hi Skip,
This is an interesting issue. I'll give you a bit of background.
In addition to my life as a developer, mechanical and electrical engineer, and data center designer, I'm also a leading expert in US philatelics. I have built a spreadsheet to help with the identification of a very complex area known as the "Washington-Franklin's" which were printed by the USPS between 1908 and 1922. There are near 500 types, so I use a "filtering" process to narrow down what one has.

I got feedback from a user community that it's a little complex for the "average user" who doesn't interact with Excel and filters, and thought it would be better if I could create a series of dropdowns that would represent the filtering. There are essentially 8 criteria in total. So I thought I could keep the entire list visible below the freeze panel header line, and put in some dropdowns to select from. Ideally, subsequent dropdowns would also "lose" options as selections above it would eliminate them.

I had thought about making some complex tables with V/HLookup to correspond to fixed dropdown selections, but I've no way to reduce the dropdown content without making it contextual.

With this in mind, any other idea for this? If you like I can provide a copy of the spreadsheet as well.

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."hammer

RE: Excel Filtering Help

Well, as the 8 criteria are selected, do you want the displayed table data filtered accordingly? I've run some multiple criteria filters on some fairly large tables and seeing the results incrimentally, was essential to me.

Yes, previous selections narrow the downstream available items.

I'm out and about now, but later I'd like to look at your sheet/table. And could you also identify the 8 columns you wish to filter on.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Excel Filtering Help

Depending on excel version, you can consider using slicers to filter tables and pivot tables. For multiple selection items can be selected either with SHIFT key or CTRL one. General info here.

combo

RE: Excel Filtering Help

If Excel 2010 or later, look up 'Slicer' in Excel help

RE: Excel Filtering Help

(OP)
Hi Skip,
Attached in the file link is the spreadsheet. On the main tab (Washington Franklin Identifier) is the data I'd like to filter.
The 8 columns are C, D, E, F, G, H, I, J (I have filter buttons on K, L and M but they aren't part of the ID criteria, so don't need to be dynamic).

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"Everything should be made as simple as possible, and no simpler."hammer

RE: Excel Filtering Help

(OP)
combo,
Oh WOW, that's AWESOME!
I used to think I knew what I was doing when it came to Excel, but my "Self-rated 8" just dropped to a 2.
This is stupid useful. I know nothing about them, but will go dig deeper, as it seems this is doing everything I need...

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate."

RE: Excel Filtering Help

(OP)
So read the details in the Excel help, which wasn't very helpful.
Quick question on this, there are a couple of slicers that have more than 1 column. Is there somewhere to specify the number? Also, some are blue, and some are orangeish... how is color set and determined?

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate."

RE: Excel Filtering Help

>how is color set and determined?

Under the Slicer Tools/Options tab

Number of columns can also9 be adjusted here.

Slicers are great.

RE: Excel Filtering Help

In the slicer's header, right corner, there is an icon to clear filter. The next to left selects multiselect mode, still in single selection mode you can have multiselection with selecting with SHIFT (continuous) or CTRL (clicked) key pressed.
In slicer's settings (right-click or options tab) you can hide items without data (as in the workbook) or left them visible and dimmed.
As you noticed, I defined structured table "t_WF', required by slicers.

combo

RE: Excel Filtering Help

(OP)
Ah, I'm feeling really old now. I keep forgetting that Office puts up dynamic tabs for various tools (this gets me in Word and PP all the time too, you'd think I'd have learned it by now).
I was trying to right click the header of the slicer itself. Knew I was missing something obvious!

Thanks combo and strongm.

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate."

RE: Excel Filtering Help

(OP)
Ok, ALMOST there... got the slicers all formatted the way I want, and this is SO cool. Way better than the drop down idea I had! I'm so glad I asked the question and give the "problem" instead of the solution.

So my last question is this. Since I have the 8 slicers (Actually 7 now I took the Design one away, as it's not needed). If I select options in 3 or 4 (which is typical in this ID), is there a way to "reset" all of the slicers back to no selection, rather than have to go and click the 'clear' funnel at the corner of each? (Is there a magic button that can be added?)

Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate."

RE: Excel Filtering Help

The Clear filters button on the data tab will do that ...

RE: Excel Filtering Help

(OP)
Ah, I was trying to use the Clear on the Home tab under Sort and Filter. But for my users, is there some way to "call" that button from a link on the spreadsheet (like upper left above the Denomination Slicer, something like "Reset All" so when it's clicked on it will cause the same thing?

The problem is my users of this are completely tech illiterate, and I want to keep all the things they need to click in the one header band.


Best Regards,
Scott
MSc ISM, MIET, MASHRAE, CDCP, CDCS, CDCE, CTDC, CTIA, ATS

"I try to be nice, but sometimes my mouth doesn't cooperate."

RE: Excel Filtering Help

ActiveSheet.ShowAllData

RE: Excel Filtering Help

ActiveSheet.ShowAllData works when one of table cells is selected.
For any selection, worksheet with single table (or to reset first table):
ActiveSheet.ListObjects(1).AutoFilter.ShowAllData

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close