Error Message with Named Range and Data Validation List w/IF Statement

[jessiew84]

Hello,

I am getting the below error message: "This list source must be a delimited list, or a reference to single row or column."

I am trying to create an IF statement within my data validation list and I have typed the following, which seems pretty basic to me. In short, I need cell D38 to return a blank value with no list options if the value in D37 is Yes. If the value in D37 is not Yes, then it should provide the drop down list for the named range OPTIONS.

=if(D37="Yes","",OPTIONS)

1. The named range OPTIONS is on a separate spreadsheet of the same workbook and occupy cell range R2:R3, which I believe satisfies the need of the data being in a delimited list.
2. D37 has been updated prior to me going into the data validation screen and entering the value Yes.
3. OPTIONS named range contains the following values:
   1. Yes
   2. No

So, as straight forward as this IF statement should be, why is it giving me this error message?

 

[cyrilbrd]

Data validation Allow: List
Source =IF($D$37="yes","",OPTIONS)

This does not work for you? What version of Excel are you using?

 

[jessiew84]

Excel 2010. I don't have the target cell as a locked cell with the "$" because I am copying this cell to other locations on the workbook eventually so I didn't want to lock it. Is this the issue?

Data validation Allow: List
Source =IF($D$37="yes","",OPTIONS)

This does not work for you? What version of Excel are you using?

 

[cyrilbrd]

Excel 2010. I don't have the target cell as a locked cell with the "$" because I am copying this cell to other locations on the workbook eventually so I didn't want to lock it. Is this the issue?

Most likely, if you will copy on several rows, lock the column.

 

[Peter_SSs]

2. D37 has been updated prior to me going into the data validation screen and entering the value Yes.

Try removing that Yes from D37. Does that allow you now to set up the following DV list?

=if(D37="Yes","",OPTIONS)