Excel Filtering Help 2

[Scott24x7]

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."

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Scott24x7]

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."

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Scott24x7]

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."

 

[SkipVought]

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,

_{Just traded in my OLD subtlety...
for a NUance!}
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

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

 

[strongm]

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

 

[Scott24x7]

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."

 

[combo]

An example of filtering structured table with slicers. As strongm wrote, option available since 2010 version, feature improving in every new excel release.

combo

 

[Scott24x7]

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.

 

[Scott24x7]

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.

 

[strongm]

>how is color set and determined?

Under the Slicer Tools/Options tab

Number of columns can also9 be adjusted here.

Slicers are great.

 

[combo]

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

 

[Scott24x7]

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.

 

[Scott24x7]

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.

 

[strongm]

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

 

[Scott24x7]

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.

 

[strongm]

ActiveSheet.ShowAllData