VLOOKUP with source data in separate workbook to the results.

[Sandy737]

Hi.
I have read that it should possible to have all the source data in 1 workbook and all the results in separate work books like a database.

Tried it, but it won’t let me get the data validation from a different workbook.

Data validation box – select list – click on “source” it won’t allow me to select from anything outside the same workbook.

 

[jasonb75]

it won’t allow me to select from anything outside the same workbook.

That is correct, you have to create a named range that refers to the source list, then use that name in the data validation box.
There are full instruction on how to do this in the link that I provided you with earlier.

 

[Sandy737]

Hi.

Still trying to create a LOOKUP with the source data in another workbook.

In the Formulas, Defined Name, Define Name, New Name box
Name: Recipe DB
Scope: Workbook
Refers to: I have tried =’RecipeDB.xlsx’!RecipeDB

Although this appears to have been accepted, when I try to set up the data validation it will not allow me to select a source data list from another workbook.

Help pls.

Source file: Recipe DB
Source data: ='[Recipe DB.xlsx]Ingredients'!$B$4:$B$300

 

[jasonb75]

Did you create the source list first?

Assuming that 'Recipe DB' is the other workbook that you refer to, you need to create a named range in the 'Recipe DB' workbook that refers to the range with the list to use for the data validation, for example

Name:- RecipeDV
Refers to:- =Ingredients!$A$2:$A$100

Then you create the named range in the workbook where you want to use the validation, this new named range refers to the first named range created above, for example

Name:- DVList
Refers to:- ='Recipe DB'!RecipeDV

Then in data validation you use =DVList as the source.

 

[Sandy737]

Hi.jasonb75

Trying to get to a stage where I have 1 workbook with all the ingredients and can create multiple other workbooks such as Starters, Mains, Baking etc, all accessing the Ingredients workbook.
Then using the tabs in the (Mains, Baking, etc..) for the individual recipes.

To simplify I have re-named the 2 starter workbooks;
Ingredients (C:\Users\sandy\Documents\Recipes\Ingredients.xlsx) 1 tab (Ingredients)
Mains (C:\Users\sandy\Documents\Recipes\Mains.xlsx) 1 tab (Chilli)*

*When it’s all working, I plan to copy each tab and change the details for the next recipe leaving all the formula intact.

Tried following the instructions but obviously doing something wrong.

 

[jasonb75]

Based on your post above, start off in the Ingredients workbook, create the following named range.

*** Note that the bold section below is for example, you should change it to the location of the actual list of items if different. ***

Name:- RecipeDV
Refers to:- =Ingredients!$A$2:$A$100

In the workbook, Mains create this named range. *** Note that the Ingredients workbook must be open ***

Name:- DVList
Refers to:- =Ingredients!RecipeDV

Now go to data validation in the Mains workbook and use

=DVList

to create the validation list.