This type of reference cannot be used in a data validation formula

[inddon]

Hello There,

I have a Master (File 1) and a Detail (File 2) workbook (Excel files attached).

I am trying to build the following in File 2:

1. Data Validation based on File 1 (List out the Currency Code from File 1)
2. Corresponding to the currency code selected, it should display the name of the currency code from File 1.

I created a Defined Name Range in File 1, and referenced it in File 2. This gives an error (attached jpeg file)

Could you please advice how the above 2 can be achieved?

Many thanks & regards,
Don

 

[Hui]

Have a read of:
http://www.contextures.com/xlDataVal05.html

 

[inddon]

Hi Hui,

Thank you for the link. I followed this link and it looks okay.

Then I created the data validation link which is based on an excel table, which gives this error.

Cannot understand where it is going wrong. Please advice.

Regards,
Don

 

[Peter Bartholomew]

Did you create the name in the detail workbook or did you try to use the original name from the master workbook?

 

[inddon]

Hi Peter,

Thank you for your reply.

In the master file Name Range is defined for the currency code
M_CurrencyCode = Tbl_Currency[Currency Code]

In the Detail workbook file in the Data Validation the Source is given as:
='Master Workbook.xlsx'!M_CurrencyCode

For reference, please refer snapshot of the error screen in original post


Regards,
Don

 

[Peter Bartholomew]

Hi Don
The thing that I tried that appeared to work was defining validation list as a Name with the Detail workbook as its parent object
CurrencyCode: ='Master Workbook.xlsx'!Tbl_Currency[Currency Code]

I also tried letting the name scoped to the Detail workbook simply refer to the equivalent name in the Master workbook but that crashed my instance of Excel.

I find it odd that Excel's ability to evaluate formulas should depend so much on the manner in which the evaluation is invoked (from good to bad):

Named Formula (evaluated from cell)
Cell Formula
Conditional Formatting
Validation by formula
Picture reference

Maybe @Hui has some insight here.

 

[Belleke]

See if this works for you.
3 different ways in example
1. with active x combobox
2. with datavalidation and formula in column F
3 with datavalidation in a table and formula in column F
Also look at the defined name ranges
Open master first!!

 

[inddon]

See if this works for you.
3 different ways in example
1. with active x combobox
2. with datavalidation and formula in column F
3 with datavalidation in a table and formula in column F
Also look at the defined name ranges
Open master first!!

OMG!. You are amazing. One challenge 3 solution options, fantastic. It works perfect. Something new to learn, I will study the same. Thank you so much.

I am allowed to click 'Like' only one time, but in the post giving 3 Smilies for 3 solutions

 

[inddon]

Hi Don
The thing that I tried that appeared to work was defining validation list as a Name with the Detail workbook as its parent object
CurrencyCode: ='Master Workbook.xlsx'!Tbl_Currency[Currency Code]

I also tried letting the name scoped to the Detail workbook simply refer to the equivalent name in the Master workbook but that crashed my instance of Excel.

I find it odd that Excel's ability to evaluate formulas should depend so much on the manner in which the evaluation is invoked (from good to bad):

Named Formula (evaluated from cell)
Cell Formula
Conditional Formatting
Validation by formula
Picture reference

Maybe @Hui has some insight here.

Thank you Peter for your response and your solution.

Please check Belleke post as well. SHe has mentioned it in 3 different possible ways

Regards,
Don