Data Validation List to External Workbook without openining source

[PocketNeo]

Here's what I have:
In Workbook "Item Tool", I have successfully linked (by Name reference only) a data validation drop down list to another Workbook "Item Database". Both separate files of course.

From Workbook "Item Tool", the column "Brand-Model" is a data validation list of all unique records in Workbook "Item Database" in column "Brand-Model".

Here's my issue:
It seems the only way I can view the drop-down list in Workbook "Item Tool" is when the Workbook "Item Database" is open.

I'd like to use the Workbook "Item Tool" as a template and would rather not have to open the Workbook "Item Database" each time I use the tool.

How can I do this?

 

[VoG]

You could use personal.xls - http://www.contextures.com/xlDataVal05.html

 

[PocketNeo]

I need this to be portable (multiple PCs) version.
Do I have another option?

 

[PocketNeo]

Ok, as a follow-up to this thread and my contribution to this great website, I have done the following that works for my needs. Hope this helps someone with non-programming ability that has the same type of needs:

1) In my main workbook (where the data validation List with the name-reference lookup exists to the 2nd workbook), I added a macro (see below) to open the 2nd workbook (the one with the items I am looking up). Oh and I didn't I didn't add any logic to check to see if it is already open.

Private Sub Workbook_Open()
' Open the workbook from its actual folder
Workbooks.Open Filename:="C:\TEST\ITEMS_DATABASE.xls"
ThisWorkbook.Activate
End Sub


2) I use my main workbook to do what I want with it (i.e. its main purpose), and upon close, I have another macro that will close the 2nd workbook if it is open.

Here's the VBA macro I used for that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wBook As Workbook
On Error Resume Next
Set wBook = Workbooks("ITEMS_DATABASE.xls")

If wBook Is Nothing Then 'The Workbook Is Not open
' Nothing to do as workbook is not open
Set wBook = Nothing
On Error GoTo 0
Else 'Workbook is open
' Close the workbook and save any changes
Workbooks("ITEMS_DATABASE.xls").Close SaveChanges:=True
Set wBook = Nothing
On Error GoTo 0
End If
End Sub

 

[PocketNeo]

Sorry, here's a better and more accurate version of what I just wrote above....

Ok, as a follow-up to this thread and my contribution to this great website, I have done the following that works for my needs. Hope this helps someone with non-programming ability that has the same type of needs:

1) In my 2nd workbook where my data exists, I created a name-reference for the range of cells that I will need for my lookup (lets call it ITEMS). In my case, its only 1 column so something like $C3:$C$25000. In the 1st workbook, where I do all my work and where the data validation List exists in a column, I created it with a name reference to the 2nd workbook and its name reference (ITEMS). So, in short, 1 workbook's data validation list name reference references another workbook name reference.

2) In my 1st workbook (where the data validation List with the name-reference lookup exists to the 2nd workbook), I added a macro (see below) to open the 2nd workbook automatically when the file is opened. Oh, and I didn't I didn't add any VBA macro logic to check to see if the 2nd workbook was already open. I know if won't open it twice anyhow.

Private Sub Workbook_Open()
' Open the workbook from its actual folder
Workbooks.Open Filename:="C:\TEST\ITEMS_DATABASE.xls"
ThisWorkbook.Activate
End Sub


3) I use my 1 workbook to do what I want with it (i.e. its main purpose is to enter quotes on each item in a order), and upon its close, I have another macro in that same workbook that will auto-close the 2nd workbook "if it is open" (i.e. in this case have add a routine to see its open and if it is, close it, and if its not open, do nothing).

Here's the VBA macro I used for that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wBook As Workbook
On Error Resume Next
Set wBook = Workbooks("ITEMS_DATABASE.xls")

If wBook Is Nothing Then 'The Workbook Is Not open
' Nothing to do as workbook is not open
Set wBook = Nothing
On Error GoTo 0
Else 'Workbook is open
' Close the workbook and save any changes
Workbooks("ITEMS_DATABASE.xls").Close SaveChanges:=True
Set wBook = Nothing
On Error GoTo 0
End If
End Sub


Hope this helps someone....cheers!