WoodyNaDobhar
New Member
- Joined
- Feb 27, 2009
- Messages
- 7
Using excel 2007, I've created a workbook that, on the primary sheet, has a dropdown (data validation) whose contents are populated (=columnName) from a secondary sheet which is populated by an ODBC connection to a mySQL db. This dropdown updates a cell next to it with the ID of the selected item. The idea is to allow the user to select from a human-readable list to select a specific ID the receiving program will recognize.
This all works fine, fyi.
There are many of these items in the dropdown (waaay too many), and all of the items have attributes that would make them easy to sort and filter, so the dropdown would have far fewer items. Any time the sheet is opened, the user will only require a single type of the specific attribute.
My question is, what is the most efficient and effective way to accomplish the required filtering?
I've attempted simple filtering, and it's not an option. If you use the built in filtering functionality in excel, it removes the unwanted items from display, but the dropdown retains them.
The user suggests a splash sheet with which he could select specifics for two or three such attributes, and have them apply to all appropriate sheets and their relevant dropdowns...there are a good half dozen db sheets to service four dropdowns. This seems like the best approach, however, I've no idea how to make it work. I've considered having dropdowns that trigger macros to change the query (adding in the requisite 'WHERE' bits) and refreshing, but I figured before I get into making that happen, I would see where you guys would go with it.
This all works fine, fyi.
There are many of these items in the dropdown (waaay too many), and all of the items have attributes that would make them easy to sort and filter, so the dropdown would have far fewer items. Any time the sheet is opened, the user will only require a single type of the specific attribute.
My question is, what is the most efficient and effective way to accomplish the required filtering?
I've attempted simple filtering, and it's not an option. If you use the built in filtering functionality in excel, it removes the unwanted items from display, but the dropdown retains them.
The user suggests a splash sheet with which he could select specifics for two or three such attributes, and have them apply to all appropriate sheets and their relevant dropdowns...there are a good half dozen db sheets to service four dropdowns. This seems like the best approach, however, I've no idea how to make it work. I've considered having dropdowns that trigger macros to change the query (adding in the requisite 'WHERE' bits) and refreshing, but I figured before I get into making that happen, I would see where you guys would go with it.
