Dropdown list from external file

[scotball]

Hey,

I have created a scorecard which has 4 key drop down lists:

- Site, Coach, Team and Agent

Coach and Team populate the dropdowns dependant on which site is selected and Agent is based on which team is selected.

I have created an external file called dropdowndata.xlsx as the main source. This file has two sheets. Sheet 1 (dropdowndata) is the main Site, Coach and Team data and then Sheet 2 (GLAteam) contains the linking Team and Agent data.

The drop down lists are based on a number of named ranges and lookups.

So the issue I am having is that the drop down lists only work if I have the dropdowndata.xlsx file open on my laptop.

Is there anyway to get this to work without having to actually open the file?

Thanks,
Lee

 

[scotball]

I didnt mean shorten.. sorry, meant more just post the exact code i should be using.

By simplify i meant more along the lines of make it idiot friendly lol...

 

[Andrew Poulsom]

First follow the instructions in the link I posted:

http://www.contextures.com/xlDataVal05.html

Then put the code I posted in the ThisWorkbook module in the workbook containing your data validation dropdown.

 

[scotball]

Ok so this is what i have so far:

1) dropdowndata.xlsx - this contains two sheets with drop down data.
2) scorecard.xlsm - this contains multiple dropdown lists referencing dropdowndata.xlsx

Where am I confused?

1) How do I get dropdowndata.xlsx to open when i open my scorecard.xlsm.
2) It must opened as shared or read-only
3) Which name range do I swap "MyList" with? I have lots of names and not sure where to begin.

 

[Andrew Poulsom]

Firstly name the two lists in dropdowndata.xlsx eg List1 and List2. In scorecard.xlsm insert 2 names:

MyList1 Refers to: =dropdowndata.xlsx!List1
MyList2 Refers to: =dropdowndata.xlsx!List2

In scorecard.xlsm data validation set the Source to =MyList1 and =MyList2 as appropriate. Add the following code to the Thisworkbook module:

Private Sub Workbook_Open()
    Dim Source As String
    Dim Pos As Integer
    Dim SourceName As String
    Source = ThisWorkbook.Names("MyList1").RefersTo
    If InStr(1, Source, ":") > 0 Then
        Pos = InStr(1, Source, "!")
        SourceName = WorksheetFunction.Substitute(Left(Source, Pos - 1), "'", "")
        Workbooks.Open Filename:=Right(SourceName, Len(SourceName) - 1), ReadOnly:=True
        ThisWorkbook.Activate
    End If
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim Source As String
    Dim Pos As Integer
    Dim SourceName As String
    Source = ThisWorkbook.Names("MyList1").RefersTo
    Pos = InStr(1, Source, "!")
    SourceName = WorksheetFunction.Substitute(Left(Source, Pos - 1), "'", "")
    SourceName = Right(SourceName, Len(SourceName) - 1)
    Workbooks(SourceName).Close False
End Sub

 

[Fazza]

Hi. You might prefer a non-code approach?

So, set up one master file with the lookup codes. It can be Excel but if it is in MS Access it might be simpler (with its automatic handling of multiple users). Say one table for each dropdown list.

Then in each Excel file set up a worksheet or worksheets with query tables to pull the lookup lists from the Access file. Suggest you google for examples of query tables. You can set the query tables to automatically refresh on file opening. So, every time the file opens the dropdown lists will refresh - no code required. Maybe best to hide the worksheet/s with the dropdown lists so that users don't corrupt them. Assign a defined name to the lists and feed these into the data validation. When using query tables, the defined names don't need to be dynamic as they will automatically adjust as the extent of the range changes.

Also, you might have some conditional formatting on the input cells so that if a selection is no longer in the lookup list it is colored red or similar. Again this can be neatly done without code.

HTH, Fazza

 

[scotball]

in the dropdowndata sheet i have over 20 named ranges - will i have to do this for all 20 ranges?

Its starting to be more complex than i imagined. :S

 

[Andrew Poulsom]

in the dropdowndata sheet i have over 20 named ranges - will i have to do this for all 20 ranges?

Its starting to be more complex than i imagined. :S

Yes you will.

 

[scotball]

what i might do is just leave it as its getting more complex than i thought :S

*sob*

 

[Fazza]

Your call whether you continue or not. Per my post above, this can be done - relatively simply - without code. If you wanted, you could put all the dropdown lists in one table if that is easier for you. (It won't matter because each individual query pulls only one field.) Leave it to you.