INDIRECT.EXT and List using Data Validation

Hi Everyone,

I am trying to create a List using Data Validation, from the INDIRECT.EXT formula.

The way I am trying to do so, is to define a Name Range "MODEL_LIST", and then do a List (using Data Validation) with this formula in Source: =MODEL_LIST

And the Name Range has this formula: =INDIRECT.EXT("'PRICE MASTER.xlsm'!&B3")

B3 is the Brand Variable which I want to change dynamically. However, this list does not work...

The list would work if I substitute with the actual brand, put it into the formula as such: =INDIRECT.EXT("'PRICE MASTER.xlsm'!COKE"), but it is not dynamic anymore.

Am I getting the syntax right? Or this method would not work? Please advise...

Jimmy

Hi JosephP

Aha! got it work now! Thank you very much.

Hi Everyone,

With JosephP's help, my above "syntax" error was resolved.

Now my problem is that, I am not able to get the List (using Data Validation) to work, when the source file is closed. When it is open, it works like a charm, but when it is closed, the List just does not react.

The formula I am using now is: =INDIRECT.EXT("'D:\FOLDER1\FOLDER2\PRICE MASTER.xlsm'!"&$B3)

Again, am I having syntax error? Or the List (using Data Validation) just does not work as what I think it should?

Thank you for anyone's help in advance.

Jimmy