Hello, I'm creating a user interface for querying data records that primarily uses drop-down menus.
The data being
queried includes many columns and thousands of rows, and creating named ranges for each drop-down menu would take too long,
and would require monthly updating. For these reasons, I'd be grateful for assistance with creating a VB script that could be
assigned to a drop-down menu which would display only the unique records for that column, based on a sequence of
user-selected drop-down menus.
The data is structures per the following example: Region (North, South, etc.; 6
unique regions); Market (Boston, Los Angeles, etc.; over 100 unique markets); Segment (Fast Food, Formal Dining; 2 unique
segments); Company (Arby's, McDonald's, etc.); Offer Type (Entree, Drink, Entree/Drink Combo, Dessert, etc.; 8 unique offer
types); Entree Type (Hamburger, Cheeseburger, Chicken Sandwich, [blank] for non-meal records; over 50 unique meal types.
I want to create sequential drop-down menus that display alphabetized results within the sub-set of filtered records,
per the following example:
-The Region drop-down menu displays all 6 unique region records for the entire record
set (North, South, etc.).
-If Region selected="West", the Market drop-down menu displays unique market records only for the West region (Los Angeles,
San Francisco, Seattle, etc.).
-If Market selected="Los Angeles", the Segment drop-down menu displays unique records only for the Los Angeles market (so if
no segment records exist for "Formal Dining", only "Fast Food" would display).
-If Segment selected="Fast Food", the Company drop-down menu displays unique records only for the Fast Food segment, filtered
for Los Angeles (Arby's, Del Taco, etc.).
-If Company selected="Del Taco", the Offer Type drop-down menu displays unique records only for Del Taco, filtered for Los
Angeles/Fast Food (Entree, Drink, Entree/Drink Combo, etc.).
-If Offer Type selected="Entree", the Entree Type drop-down menu displays unique records, filtered for Los Angeles/Fast
Food/Del Taco/Entree (Burrito, Taco, etc.).
To sum up, this sequence of user-selected drop-down menus would
filter out records for unrelated records, such as hamburgers at McDonald's in Boston, pitas at Falafel King in Minneapolis,
and so on.
http://tinyurl.com/3pd89d8 describes what appears to be part of the solution, but I'm such a VB novice
that I don't know how to render that declaration into a drop-down menu, much less how to apply it to so many sequential
drop-down menus. As mentioned above, using named ranges and INDIRECT in data validation would require dozens of work hours
every month, so the solution would need to be automated by VB.
Thank you for reading! Any assistance would save my
skin (and maybe my job). I also welcome fee-based help offers that could help out in a pinch.