Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box lookup
Please bear with me on this one...I hav
a worksheet that lists truck drivers, locations they service, rout #, truck # and some other related info. I have the auto filte turned on and I have everything in seperate columns except for th locations, they are in a single column seperated by a comma. Som drivers may have up to 6 different locations they cover in a give day, others have only 1. I have standardized the cities so that they have common abbreviations I have made a list of the cities and their abbreviations and linke them to a combo box for easier reference. Then I have to eithe "find"(ctrl + F) the city I'm looking for or do a "custom" searc under the "cities" column (using auto filter). Both of these option work but I would like to search directly from the comb box so that when a city is selected the auto filter wil automatically do a "contains" search on the column and only show th rows with that city liste I know I could do a macro but I'm not sure if thats the best option. I have several people using this that don't know Excel at all so I' trying to make it as simple as possible Any help/ideas/suggestions are appreciated Cla -------- www.coffeecozy.co Use your Bodum and give up cold coffee for good! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box lookup
To me, the easiest non macro approach would be to use the next available
column and put in a formula like =if($M$1="","",if(isnumber(Search($M$1,F2)),"Yes", "No")) Say you city data is in Column F and this formula would be in G2. The city name from the combobox would be in Cell M1. Now drag fill down the column. Then you can apply your filter to this column for the value Yes. If you wanted instant results, you could have the click event of the combobox reapply the filter criteria using a short macro. -- Regards, Tom Ogilvy "CEN7272 - ExcelForums.com" wrote in message ... Please bear with me on this one...I have a worksheet that lists truck drivers, locations they service, route #, truck # and some other related info. I have the auto filter turned on and I have everything in seperate columns except for the locations, they are in a single column seperated by a comma. Some drivers may have up to 6 different locations they cover in a given day, others have only 1. I have standardized the cities so that they have common abbreviations. I have made a list of the cities and their abbreviations and linked them to a combo box for easier reference. Then I have to either "find"(ctrl + F) the city I'm looking for or do a "custom" search under the "cities" column (using auto filter). Both of these options work but I would like to search directly from the combo box so that when a city is selected the auto filter will automatically do a "contains" search on the column and only show the rows with that city listed I know I could do a macro but I'm not sure if thats the best option. I have several people using this that don't know Excel at all so I'm trying to make it as simple as possible. Any help/ideas/suggestions are appreciated. Clay --------- www.coffeecozy.com Use your Bodum and give up cold coffee for good! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box lookup
I'm new to VBA. Could anyone give me some good sites for reference to
build the macro for this appl. Another Note about this sheet. The combo box references a column that has concatenated two columns that contain the city name and abbreviation. I would like to reference, just the column with the abbreviations for the filter. I'm sure this isn't difficult but I'm just not sure where to start, so if someone could help by pointing me in the right direction, I would appreciate it -Clay --------- www.coffeecozy.com Use your Bodum and give up cold coffee for good! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup on a combo box | Excel Worksheet Functions | |||
Need a Lookup/Sumif Combo Formula | Excel Worksheet Functions | |||
combo boxes and lookup | New Users to Excel | |||
COmbo box and lookup list | Excel Programming | |||
Combo Box wrong lookup range. | Excel Programming |