Data Validation - error

[Evapar18]

I have a data validation with a nested if statement.

=IF(R8="D18",D18_OP,IF(R8="D24",D24_OP,IF($R$8="D34",D34_OP)))

It works fine as long as I don't have D18 in R8. D18_OP named range = "Not Applicable". When D18 appears in R8 the drop down will not load with "Not Applicable" nor can I select it. I cannot change it to any text either. I tried replacing the Named range with "-", but it continues to fail.

I have several more of these I need to make where either D24 or D34 is not going to load anything into the Data validation drop down.

What am I doing wrong or how can I get around it?

 

[Evapar18]

I think there is something seriously wrong with my workbook as I tested this in another workbook and it works fine.

Is there any software to find and fix workbook errors in 64-bit for free?

 

[Evapar18]

OK this is messed up. I started a new workbook copied over some of the data and started new with Data Validations and the problem is back. The one I battled yesterday still works. If I copy that Data validation over and change the formula its back not allowing me to select the data if D18 or D24 is selected.

=IF($R$8="D18",D18_OPLUG,IF($R$8="D24",D24_OPLUG,IF($R$8="D34",D34_OPLUG,"-")))

Here is a link to the excel file: https://drive.google.com/file/d/1xsXec5csg6zMrqZwsXvBNHbhVoCSdF3I/view?usp=sharing - See "Sheet2 C24"

What am I missing here?

thanks,
Scott

 

[RoryA]

The named range needs to refer to a cell containing that text, not to a literal string.

 

[Evapar18]

Thanks for the reply, I will give that a try

Any idea why it works for the one in Cell C16?

 

[RoryA]

You have three D18_OP names defined. The workbook level one, which is what the DV is using, refers to ='Options Vertical Layout'!$B$11 and not to a literal string.

 

[Evapar18]

THANK YOU!!! Very much appreciated. I knew it was something stupid, on my part

 

[RoryA]

Glad to help.