Filter Results Shown In Data Validation List

My earlier post on this subject did not yield me an answer, so I am trying again. Here is the link to that post “Add Filter To Cascading Combo Boxes”. I hope it's not bad practice to start a new post instead of continue the old one. If it is, then I hope someone merges these threads instead of deleting this one.

This time instead of posting my real spreadsheet, I am posting a much simpler example, hoping for a better response.

Please download the attached example spreadsheet. On the workbook “Main” you will see cell “D2” is highlighted in yellow. That combo box (based on a data validation list) shows all the divisions that can be chosen. If you go to the workbook “CSI” you will see a sample of the data that I am working with. Each row of data that starts with a “00” is in Division 00, and each row that stars with “01” is in Division 01, etc.

Once a division is chosen in cell “D2”, then cell F2 looks up a named range. Then the green cells (D6 through D15) are limited to only those items that fall within the division that is chosen in cell “D2” by use of the data validation formula “=INDIRIECT($F$2)”

I am looking for a way to filter the results of these second combo boxes (data validation lists D6-D15) further. Instead of showing all the results of the named range which is shown in cell “F2” I would like it filtered. The filter would be based on whether or not the last column of data for any given row has a “1” in it. It doesn't have to be a "1", I'll accept any way that you can make this work. I am just using a "1" for this example. Whether or not a row contains a "1" in column "J" will change from time to time, and I am thinking that this will be an easy way to indicate, or flag, whether or not a row is "Preferred", but I'm open to other suggestions.

If the cell in that row, row “J” on worksheet “CSI”, contains a “1” then I would like it to be displayed in the data validation lists in cells D6 through D15. If the cell for that row of data is blank then I do not want it to show up in the data validation list combo box.

To complicate this further, I would like a way to easily switch this filter on and off. I imagine this would filter would be turned on or off based on what is selected in an option button group (like I’ve got highlighted in blue in cell “D4”, whose results are tied to cell F4), or through an additional combo box or data validation list.

I'm pretty stuck from moving forward on this project until I get over this hurdle, so I appreciate and help you can provide.

Thanks, Spence

Originally Posted by NBVC

Generally, you should not be starting a new thread... but I see that the other one has lead to some confusion... so perhaps this time you can show the link to this thread in the other one so that the responder(s) know that you have continued elsewhere in case they may want to continue assisting you... or so that others don't answer over there....

Done, thank you.

See attachment.

WHER, thank you for taking the time to come up with a solution. I'm not familiar with a lot of the logic you are using, so it will take me a while to try and absorb it. It does seem to work great.

Thanks, Spence

Marcol, thanks for still working on this. I wasn't sure if you had given up or not. Those first four formulas you are showing make great sense and are definately a lot cleaner than the way I was doing it before.

I had not given up on using the Data Form as a search method. I just wanted to make the example in this post as simple as possible so I could potentially get a solution for my main roadblock which is filtering the data validation list.

I love how you got the Data Form to appear right on the "Test" workbook, instead of having to go look at the blank "Find" workbook like I was doing.

I'm not sure what you are doing with the EditOptionLevels macro, so please let me know what your intentions were there.

Do you have any suggestions on filtering the data validation list, or is the method that WHER is suggesting going to be my best bet?

Thanks, Spence

Originally Posted by WHER

See attachment.

I've tried to reverse engineer your logic, but there are too many functions in your formula that I haven't used before to try and grasp all at once.

From what I can understand, your approach is quite clever because you are using the values of the data itself to get the results. I have been focusing on finding a solution that involves my named ranges, but you don't seem to need to with your method.

I don't want to use a formula that I don't understand, so although you have created a solution to my problem, I'm going to have to keep looking for a different solution, or give up on my idea of being able to filter this data validation list until I can take the time to learn everything that you have going on in that formula.

I will keep trying to learn what you have showed me, I just don't have the time right now that it looks like it would take me to figure it out before I complete this spreadsheet.

Do you have another solution that would work if my data was not formatted so nicely? Where you couldn't rely on the contents of the data in column "A" and had to rely on the named ranges and whether or not there was a "1" in column "J"?

Once again, I thank you for your proposed solution.

Spence

Remember time zones, where are you located?

Everybody on this forum gives time, and ideas, free gratis and for nothing....

Have a little patience...

I intend having a further look later today (23-Dec-10) during normal UK times

Hi Spence,

I tried creating a similar formula, using your named ranges but didn't succeed.
My existing formula's should still work if the data in column A would not be nicely grouped, as they are now.

Originally Posted by Marcol

Remember time zones, where are you located?

Everybody on this forum gives time, and ideas, free gratis and for nothing....

Have a little patience...

I intend having a further look later today (23-Dec-10) during normal UK times

Marcol, I understand and appreciate what you are saying. I am trying to be patient, it's just that I'm getting desperate. If I don't come up with a solution to easily filter all these 7,314 rows, then my boss will insist I just delete the one's we think we don't need.

I know that will lead to regret down the road, so I'm on a mission to "save the data".

I am located in Michigan, in the United States. I'll go edit my profile to reflect that. It would be a nice feature if under the area where it shows each posters location it would also show their local time in relation to ours.

I appreciate your continued assistance and look forward to your next reply. Even though I couldn't totally understand everything that WHER had done in his formula, it did give me some ideas on a different approach to take. I'll outline those below in my reply to him. Please take a look at that before you spend more time on this.

Thanks, Spence

Originally Posted by Spencer

Marcol, thanks for still working on this...

I had not given up on using the Data Form as a search method. I just wanted to make the example in this post as simple as possible so I could potentially get a solution for my main roadblock which is filtering the data validation list.

I love how you got the Data Form to appear right on the "Test" workbook, instead of having to go look at the blank "Find" workbook like I was doing.


Thanks, Spence

Marco, I played around a bit more with the Data Form last night. It's great to have it show up on the "Main" worksheet instead of the "Find" worksheet. It would also be great if I could eliminate the "Find" worksheet altogether as that makes the file size of the spreadsheet unnecessarily large.

The reason I created the "Find" worksheet in the first place was that I wanted the Data Form to be laid out in a certain way, different than the way my columns were laid out in the "CSI" worksheet. I didn't want to have to go back and rename all my Named Ranges in the "CSI" worksheet, so I just copied the data over into "Find" and reorganized it.

When this data validation problem gets solved and I go back and start over from scratch, rebuilding my spreadsheet the right way, I am pretty sure I can reorganize the data in the "CSI" worksheet so that if a Data Form is based on it that it will look the way I want.

Because I had removed all locks and protection the worksheet that you had to work with, I was able to copy the information in the "2010 Code And Description" field before I hit the close button on the Data Form. Then I was able to paste it directly into one of the cells on my "Test" worksheet.

That was beautiful, because it prevented me from having to either remember, or write down that information and after I pasted it, and the two lookup fields to the left still worked fine. It would be a huge timesaver if I could retain that copy and paste functionality.

I can't leave the underlying data that the Data Form is based on unlocked and unprotected though, because it could too easily get corrupted by someone inadvertently making a mistake while working with the Data Form.

With that underlying data protected though, I am not able to select any of the information so I can copy it. Do you know of a solution to that?

Also, if I do base my Data Form on the “CSI” worksheet, there will be a lot of extra columns in that worksheet that I don’t want to have show up on my Data Form. Is there a way to change the "Find" macro to somehow limit (or select) the range of data that the Data Form shows?

Thanks, Spence

In response to post #12, see attachment.

Marcol, I get an error due to this piece of code when a particular CSI category does not have any items marked as Preferred.

Please Login or Register  to view this content.

It is trying to subtract one from zero. Do you have a quick patch you can show me to prevent errors in the unlikely case that a particular CSI category has no preferred items?

Thanks for all your help,
Spence

Originally Posted by Marcol

Can't remember the full code, but try

Please Login or Register  to view this content.

I tried something similar myself and was able to get past that line of code, but it generates an error further down in the code:

Please Login or Register  to view this content.

If you could take another look at this I would really appreciate it. You posted most of your code in Post #14 on page one of this thread. The spreadsheet itself with all your code in it is also attached to that post.

Marcol, after I posted my reply above I thought of something to try which seems to work. Please let me know if you see any problems with my solution as it does seem to work fine.

Please Login or Register  to view this content.