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