Add Filter To Cascading Combo Boxes

[Spencer]

I currently have two separate combo boxes created by using data validation. The first combo box selects one of 35 divisions of items. The second combo box then only shows the items that fall within the division that is selected in the first combo box.

I'm not sure if I'm using the best method to accomplish that, but it seems to work just fine. What I need to do now is to somehow filter the results that show up in that second combo box by another criteria other than just the division that is selected in the first combo box.

The total amount of data that makes up all 35 divisions is 7,314 rows long. In reality, we will only regularly use a fraction of that data, maybe 200-500 rows on a regular basis. I don't want to delete the rows that we don't normally use, because every once in a while we will want to use that data, just not very often.

What I would like to do is add an additional column to each of my 7,314 rows where I could identify whether or not that row was "preferred", by having that cell contain an "x", "p", or "1", etc. Then I would like to somehow filter the results of my second combo box to either show me "all" results, or just "preferred" results. We would normally filter the results to "preferred", but then when we needed access to an item we don't usually use, it would be available to us by removing the "preferred" filter.

I don't want to make this post too long, but in case you need to know the current methods I'm using, I will explain them below.

My first combo box is based on a data validation list. That list is the first column of a table which contains the names of all 35 divisions. The second column of that table contains the corresponding names for the named ranges to be used for the second combo box. For instance, the named range for Division 01 is SheetName!A2:A255. So when Division 01 is selected in the first combo box, my second combo box displays all the names in SheetName!A2:A255.

I would like to have an option button group or an additional combo box that I could use to further filter the results to show just my "preferred" items. So instead of showing me all 254 names contained withing Division 01, it would show me only those 10-20 names whos "preferred" column have been flagged with an "x", "p", or "1", etc.

It's OK if I need to scrap my current methods and start over in order to accomplish this, so if there is a better way of going about this that the road I'm on, that's perfectly OK. The same goes for my idea of adding that "preferred" column, if there is a better way to somehow "flag", or "tag" a particular row as being "preferred" that's fine.

Thanks, Spence

Using Excel 2007

[Spencer]

I am really hoping to have some direction to go on this by morning, so if anyone has any suggestions at all I would appreciate it. If what I am trying to do is not able to be done in Excel, I would appreciate knowing that as well.

Thanks, Spence

[lordyoyo]

I don't really understand what you're trying to do but based on what I have understood, wouldn't this be easier to do with Filters?
For better understanding, it would be very helpful if you could upload the file/a sample file with the same problem.

[Marcol]

This should not be difficult to do, using VBa.

You would do best to post a sample workbook showing your desired lists and where they have to be applied.

It should clearly illustrate your problem and not contain any sensitive data.

[Spencer]

This should not be difficult to do, using VBa.

You would do best to post a sample workbook showing your desired lists and where they have to be applied.

It should clearly illustrate your problem and not contain any sensitive data.

OK Guys, here is the spreadsheet. This is a macro-enabled workbook, so I don’t think it will work right unless you enable macro’s. Don’t worry, as you will see by my work, I am not nearly clever enough to devise anything dangerous, and even if I was, I would never do such I thing.

Here is the background on this spreadsheet project. I work at a construction company and we use Excel spreadsheets to calculate our bids for projects. We use CSI Codes to classify each different item in our bid. The total list of 2010 CSI codes is 7,314 rows long. I needed a way to not only choose from those 7,314 2010 CSI Codes, but to also cross reference them to the older 2004 and 1995 CSI codes. On top of that I also needed to be able to cross reference them to something called UCI Codes. I’ve used the four option buttons for the cross-referencing and that seems to work fine, but I’m open to suggestions.

So we will first pick one of 35 divisions in the yellow combo box in cell “B2”. Then each combo box in cells C6 through C35 will only show items that fall within whatever division is listed in cell “B2”.

As I explained in my original post, I would like to be able to filter the items available in those combo boxes in cells C6-C35 to show only our preferred CSI codes. Please re-read my original post for more on that subject.

Most of the workbooks will normally be protected and hidden. Only “Test” and “Find” will be visible. I’ve unhidden and unlocked them all (I think) so you can see everything.

The workbook “Test” is the main workbook, “Find” is something I had to create in order to have a search option. I would love a better solution than the “Find CSI” button/macro I have created in cell “B5” that would allow me to search for keywords, so if you have any ideas on that please pass them along.

What I need to accomplish with “Find CSI ” is a way for someone to search for say “concrete” and then be shown every CSI Title that has the word “concrete” somewhere in it. The way I am currently accomplishing this is to hit the “Find CSI” button which brings you to the “Find” workbook and opens up the Data Form dialog box.

Then you would hit the “Criteria” button and in the “2010 Title” field you would type in “*concrete*”, and then hit the "Find Next" button as many times as needed until you found the CSI code you were looking for. Then once you write down the code you found, you hit the “Close” button and it brings you back to the “Test” workbook where you can choose a combo box and scroll down until you get to that code you just looked up. I know it’s very rudimentary and I’m sure there is a more elegant solution, but it’s all I could come up with so far.

I’m getting off subject though, so I’ll wait for any questions you have on what I’ve done, or what I’m trying to do. Keep in mind that this workbook called "Test" is just one of what will be many similar workbooks that will have their totals and other data pulled up to a summary workbook. We have primarily had 16 Divisions in our line of construction, but the current 2010 CSI standards have 49 Divisions. I don't think we'll have many more than our original 16, but that is something I still have to figure out. It would be awesome if I could start out with just one for our Divison 01 General Conditions and then somehow be able to add additional workbooks on the fly as needed, but that I'm sure if even possible, is way above my current skills.

I think my solution to filter my combo box results may be able to be accomplished with a SumProduct and ISError formula in my conditional formatting, but I need to research that more.

I welcome all ideas, solutions, and suggestions, but I'm not looking for someone else to do all my homework for me. I want, and need, to thoroughly understand everything that happens in this spreadsheet, so I don’t want to end up with a bunch of VBA that I don’t understand. If VBA is the solution, which I understand it usually is, if I am going to use it I need to have a way to understand it. So if you are going to suggest VBA code, please try to explain what you can.

Also, as I stated above, there are no malicious macro’s or code in my spreadsheet. I would prefer to keep it that way, so I would be much more comfortable editing my spreadsheet on my end as opposed to downloading one that has been modified and re-posted. I know that is kind of crappy of my to ask for your help and then not fully trust you at the same time, but I don’t know enough about malicious stuff to know how to detect it.

Thanks, Spence

[MarvinP]

Hi Spence,

What you are trying to accomplish is still a little out of my understanding. I see you have named ranges and that is good, if you use them. I don't see in the VBA where you have used the range names.

Excel does Tables and Filters very well and fast. Is there a way to put your data in a table and then use what you've selected in your combo box to filter the table? Auto Filters or Advanced Filters may be appropriate for you.

Pivot Tables are another feature in Excel that allow filters above the table and are very slick. I'm not sure they will work for your specific application.

Also, I had some problems opening your attached file and the Find tab was blank.

If you could create a smaller and more direct example it might help getting a solution.

[Marcol]

Okay, once I found a few of your booby-traps with custom formatting, etc...and unveiled Sheet Find etc..., It seems to me that your approach is basically sound, just rather large.

I am not clear how you want to sub-define your "Not in regular use" lists.

It seems that you already handle named ranges reasonably confidently.
My first reaction is to suggest that you split some of these lists and add "Less Commonly Used" to the main part, and "Commonly Used" to the lesser part.

You could then toggle these Validation Lists using the sheet change event when either list item is selected from the drop-down.

It will take some time to understand your required logic, if you could briefly explain your rules, or point to where you may have already flagged them, it would help.

Cheers

[Spencer]

[QUOTE=MarvinP;2437993]Hi Spence,

What you are trying to accomplish is still a little out of my understanding. I see you have named ranges and that is good, if you use them. I don't see in the VBA where you have used the range names...

...Also, I had some problems opening your attached file and the Find tab was blank.[QUOTE]

Marvin, thanks for taking the time to look at it. I only used the named ranges in my data validation rules for my combo boxes.

Sorry about forgetting to reset my formatting on the "Find" worksheet. Just "Select All" and then change the format to "General" and everything will appear. The "Find" worksheet is basically a copy of the "CSI" worksheet. It is just has the columns reordered so that they appear the way I want on the Data Form. There are a couple of more cells with that invisible formatting on the "Test" worksheet. You'll find them at B3, C2, and C3.

I should be able to do away with the "Find" worksheet all together on my final product. I only added it because I already had all my ranges named on my "CSI" worksheet and I didn't want to have to rework them all in order to have them show up the way I want them displayed on the Data Form.

[Spencer]

Okay, once I found a few of your booby-traps with custom formatting, etc...and unveiled Sheet Find etc..., It seems to me that your approach is basically sound, just rather large.

I am not clear how you want to sub-define your "Not in regular use" lists.

It seems that you already handle named ranges reasonably confidently.
My first reaction is to suggest that you split some of these lists and add "Less Commonly Used" to the main part, and "Commonly Used" to the lesser part.

You could then toggle these Validation Lists using the sheet change event when either list item is selected from the drop-down.

It will take some time to understand your required logic, if you could briefly explain your rules, or point to where you may have already flagged them, it would help.

Cheers

Marcol, I appreciate you looking at this as well. I don't currently have any of the data flagged or tagged as being preferred. Here is how I would like to flag the rows of data as being preferred:

The first empty column on the "CSI" worksheet is column "J". I would like that named "Preferred". Then for each row of data that I would like to be recognized as being preferred I would enter a 1, or a P, or an X, etc.(some sort of identifier that can be used in my data validation) into that column "J".

Then my data validation rule which covers cells C6 through C35 would have to be changed to somehow filter out all rows which do not have that flag in column "J". However, I would need some way to turn this filter on and off, so that if someone using my spreadsheet needs a CSI code that is not currently flagged as "Preferred", they could hit an option button, or change the value of a combo box and then the combo boxes in cells C6-C35 would no longer be filtered by "Preferred", they would only be filtered by the division selected in cell "B2".

The list of what is considered "Preferred" will be changing quite frequently over the next year or so until we really get a good list put together. Therefore I don't want to just rearrange the order of the 7,314 rows of data. If I did that I would have to rearrange them every time I wanted to add or remove something from as being "Preferred".

If I could have this column "J" as the indicator on whether or not a particular CSI code is preferred or not, I would simply have to add or delete that identifier from that column. I wouldn't have to worry about renaming any named ranges or rearrange any of the data.

Thanks, Spence

[Spencer]

Due to the confusion caused by my spreadsheet example and my multiple attempts to explain what I am trying to do, I have started a new thread on this subject.

I hope the new thread, and spreadsheet example, is simpler to understand and will lead to better results.

Please see the new thread "Filter Results Shown In Data Validation List" and post any responses there.

Thanks, Spence