Named range in separate workbook in data validation drop down list

[Will85]

Hello,

I would like to reference a named range in another workbook in my data validation drop down list, but I am having difficulty.

The external workbook containing the named range is 2019 COA.xls
The named range in the workbook that I want to reference is Revenue_Categories

I tried several different methods but was unsuccessful.

 

[Will85]

I have solved this issue by Defining a name and using that function to reference the outside workbook, then entering that new name into my data validation list. So now my data validation list has a drop down using a named range in another workbook.

However, I use indirect in another cell to further narrow down the data, and so now need to figure out how to use indirect to reference a second named range in the outside workbook via the first named range in the outside workbook.

For example I have 10 lists, each list has 20 items in it. My data validation allows the user to select which list (1 through 10) then I use indirect in another cell based on the first selection to show the corresponding 20 items in that list.

Since the lists are now in another workbook, I am struggling getting the indirect to work.

 

[Will85]

Help!