Excel 2000: Combo/Drop Down Box linked to another Workbook

[LINSEY]

I'm trying to create a form in an excel spreadsheet where you can select a "style" from a drop down menu and based on the selection populate additional cells.

The "style" list would come from a database ("Fabsheet Database") in another excel workbook. Here's the basic layout of "Fabsheet Database":

STYLE[tab][tab]VERSION[tab][tab]A/I[tab][tab]COLOR
LYW[tab][tab][tab]000[tab][tab][tab][tab]I[tab][tab][tab]CARBON
LYW[tab][tab][tab]001[tab][tab][tab][tab]A[tab][tab][tab]CARBON
MTS[tab][tab][tab]000[tab][tab][tab][tab]I[tab][tab][tab]CARBON
MTS[tab][tab][tab]001[tab][tab][tab][tab]I[tab][tab][tab]CARBON
MTS[tab][tab][tab]002[tab][tab][tab][tab]A[tab][tab][tab]MIDNIGHT
MTS[tab][tab][tab]003[tab][tab][tab][tab]I[tab][tab][tab]MIDNIGHT
PTR[tab][tab][tab]000[tab][tab][tab][tab]I[tab][tab][tab]VINTAGE
PTR[tab][tab][tab]001[tab][tab][tab][tab]I[tab][tab][tab]VINTAGE

My form should have drop down box that lists only the active styles (an "A" in the "A/I" column means it's active). In the example above the active styles would be LYW and MTS, but not PTR. I can create the drop down with a list of all styles, but I haven't figured out how to list only the active styles.

When the style is selected from the drop down list, I need to populate another cell with the "Color". For example, when LYW is selected, "CARBON" would appear in another cell. BUT, I need the information to come only from the "active" version. For example, when MTS is selected, it populates the cell with "MIDNIGHT" (because that is the color of the "active" version).

Someone please help! I'm not sure where to start on this. If drop downs are not the best way to accomplish what I need, then please give me another suggestion.

Thanks,
Linsey

 

[LINSEY]

Can anyone give me a suggestion on where to start with the drop downs?? I have no experience with VB code.

Thanks,
Linsey

 

[DaleWatson123321]

Linsey,

Perhaps the reason you haven't yet received any responses is due in part to your task being a "little involved". And without you having VBA experience, plus potential contributors not being able to see the "scope" of your application (by viewing your file or at least part of it), conveving the proper steps to you becomes more of a challenge - at both ends.

A preferable approach in such cases would be for you to email a copy of your file, with any sensitive data replaced with fictitious data that still reflects the type of data you're working with.

From what you've described, here's a brief list of the steps I would see being used (with VBA)...

1) Import the data into one (separate) sheet.

2) Using VBA code associated with Excel's "Advanced Filter", extract the data required for your drop-down list to another (separate) sheet - where it will automatically become your (updated) drop-down list.

3) Once the user makes a selection from the drop-down list, a function such as VLOOKUP or DGET can be used to identify the Color.

If you'd like to email the file, I'll be pleased to help you out with the specifics.

Because Tek-Tips requires that contributors NOT use email addresses due to spam related problems, you'll need to replace the "at" with "@" in the address below.

Regards, Dale Watson dalwatson at gov.mb.ca

 

[LINSEY]

Dale,

Thanks for the offer to help. I'm emailing you my files.

Linsey