Selecting multiple items in a drop down list

**ACM-GFT** · 12-09-2008, 09:33 AM

I've made a database for my work using excel. Essentially the user just puts in the name of a new resource and then enters details about them from data validation drop down lists defined on another page of the worksheet. When a client comes along they can use data filtering on the list to look up all resources in a specific location or sector for example, there's several different criteria. The thing is, some resources operate in several regions and sectors and so the records are incomplete.

There's a number of ways to get around this that I know of, creating more than one record per resource for example where each variation of their details is entered. However, if for example they operate in three regions and four sectors I'll need to create twelve records just for that one resource which would get pretty time consuming. Another thought I had was that I could simply make multiple columns for each category so that you can make multiple selections that way but some of the resources could easily have upwards of five or six selections per category. All these solutions seem a bit clumsy to me though so I've been trying to come up with a way to be able to use check boxes or something similar in the drop down list. Is there any way, for example, to be able to select sectors 4, 6, 10 and 13 for example from a single drop down list and have it show up when any of them are selected as a filter on the list? I've attached an example of my prototype to try and make things clearer, hopefully it does so.

I've scoured the web and help files looking for a solution on my own but I don't know if what I'm after is really possible with Excel's functionality. Hopefully I'm wrong though, does anyone have any ideas?

Thanks in advance all.

**ACM-GFT** · 12-09-2008, 12:02 PM

Okay, looking around I've managed to find a way to do more or less what I'm after. I've used some copied code from an example site to allow items selected in one column to be shown, seperated by commas in the next. I've then created a bunch of search macros for each criteria and a reset macro so that it's just a bunch of buttons to perform multiple searches.

The code I used is:

Please Login or Register  to view this content.

The problem is, I can't figure out how to duplicate the code or alter it so that it runs in the different columns. I've tried copying and pasting the code and altering the number shown in the target column part of the code and things like that but I'm a complete newbie when it comes to VB coding. I've attached a copy of what I've got so far, can anyone tell me how to make the code apply to the rest of the criteria, not just the location column?