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

jessiew84

New Member
Joined
Sep 18, 2014
Messages
5
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?
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

cyrilbrd

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Data validation Allow: List
Source =IF($D$37="yes","",OPTIONS)

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

jessiew84

New Member
Joined
Sep 18, 2014
Messages
5
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

Well-known Member
Joined
Feb 2, 2012
Messages
4,071
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,329
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,484
Messages
5,529,125
Members
409,851
Latest member
Ingar
Top