DropDownList source Indirect NamedRange from Another Workbook

[OxmoPuccino]

Hi,
I created a file, Discography.xlsx, "Sheet 1": column A contains the names of Music Albums and column B contains the names of the tracks of the album in column A.
I selected all the tracks within an album from column B and Defined Name, the name of the album.
Sheet 2: All the albums are in column A and I selected them and Defined Name: Albums.

I created a second file, BestOf.xlsx, I opened the "Define Name" window:
Name: Albums
Refers to: =Discography.xlsx!Albums
(I did the same procedure to create names for each albums)

I created a drop down list in A2, Data Validation:
Allow: List
Source: Albums

So far it works since I can select the album name from a drop down list in my file BestOf.xlsx (as long as my file Discography.xlsx is open as well, an hindrance I can live with).
This is where I can't make it work.
File "BestOf.xlsx" cell B2, I want a drop down list that only gives me the choice of tracks from the album selected in A2.

What I did, Data Validation:
Allow: List
Source: Indirect(A2)

This doesn't work and I don't understand why since it works if the name range doesn't come from another file.

[I know I can't have space in name ranges so I've used underscore everywhere]

Thank you for your help!

 

[OxmoPuccino]

Discography.xlsx "Sheet 1"

Discography.xlsx "Sheet 2"

BestOf.xlsx "Sheet 1"

 

[OxmoPuccino]

The solution is Power Query.
Give it a goog!

 

[smozgur]

If you would like to post a solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[OxmoPuccino]

By using "Power Query" I was able to easily link my data from "Discography.xlsx" to a dedicated tab in "BestOf.xlsx" and every time I re-open my file "BestOf.xlsx", it refreshes the data being pulled from "Discography.xlsx".
This way I was able to use a dependent drop down list with the indirect function since the define name was no longer being linked to a different workbook.