data validation & linking box's

In the sheet VehicleMakes.xlsx, click on INSERT > NAME > DEFINE

Names: Alfa_Romero
Refers to: =[vehicle_makenmodel.xlsx]vehicle_makenmodel!$B$1:$B$18
ADD

Names: Audi
Refers to: =[vehicle_makenmodel.xlsx]vehicle_makenmodel!$B$19:$B$52
ADD

...etc...create a named range for every company...with _ instead of spaces in the two-word company names.

Next, click on B1 and go to Data Validation
Allow: List
Source: =INDIRECT(SUBSTITUTE($A1," ","_"))
OK

Then copy that cell down.

Another Approach is:

Please Login or Register  to view this content.

See file

PS: one book and two worksheets

Dunsdale, those are step by step instructions on how to setup the dependent drop down lists.

You have to create a NAMED range for every one of the company names. A named range is a group of cells that you refer to by NAME. Instead of A1:A10, you give those cells a name of "Ford" and then you can use that name in formulas or in Data Validation. Does that make sense?

So, step by step, this is how you make a named range. You'll need to open the Make & Models book so you can see what the ranges in the B column should be.

Then, in the sheet VehicleMakes.xlsx, click on INSERT > NAME > DEFINE

Names: Alfa_Romero
Refers to: =[vehicle_makenmodel.xlsx]vehicle_makenmodel!$B$1:$B$18
ADD

Names: Audi
Refers to: =[vehicle_makenmodel.xlsx]vehicle_makenmodel!$B$19:$B$52
ADD

...etc. Continue creating a named range for every company. Named ranges can't have spaces, so we replace the spaces between words with _ instead of spaces in the two-word company names. So "Alfa Romero" becomes a named range of "Alfa_Romero". OK?

Now, let's add the Data Validation to column B. Click on B1 and go to Data > Validation

Allow: List
Source: =INDIRECT(SUBSTITUTE($A1," ","_"))
OK

Your drop box arrow should appear in B1. Click on it and you will see all the cars that match the "make" to the left in A1. Once you have that cell working properly, copy that cell down the rest of the B column and all your named ranges will kick in.


Column B is using the INDIRECT() method to grab the "word" in column A and go find the named range of cells that match that word. So if the word in column A is "Chrysler", and you previously defined a named range s "CHRYSLER" and pointed to the correct cells in the other workbook, those cars should be in the drop list now.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)