Data Validation - error

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
60
I have a data validation with a nested if statement.
Code:
=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?
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
60
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

Board Regular
Joined
Aug 3, 2018
Messages
60
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.

Code:
=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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The named range needs to refer to a cell containing that text, not to a literal string.
 

Evapar18

Board Regular
Joined
Aug 3, 2018
Messages
60

ADVERTISEMENT

Thanks for the reply, I will give that a try

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Board Regular
Joined
Aug 3, 2018
Messages
60
THANK YOU!!! Very much appreciated. I knew it was something stupid, on my part ;)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,103
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :)
 

Forum statistics

Threads
1,136,314
Messages
5,675,021
Members
419,543
Latest member
Casp

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top