Data validation with conditional list

[thbutterflycollector_]

Hi,

I am trying to apply a data validation list in Cell C2 which should apply if a condition is met. I have input the following formula:

=IF(B2<>"Disallowed","N/A",'Register'!$B$7#)

However rather than yield a result of "N/A" the cell value is showing as an item from the Register. (This is a drop down list that hides already used items).

I am not sure where I am going wrong here. Can anyone assist?

 

[etaf]

any spaces in the cell B2 with
"Disallowed"
"Disallowed "
" Disallowed"

 

[RoryA]

Data validation will not return a value to the cell, it will only affect what is allowed to be entered.

 

[thbutterflycollector_]

any spaces in the cell B2 with
"Disallowed"
"Disallowed "
" Disallowed"

No - this itself is data validation list.

 

[thbutterflycollector_]

Data validation will not return a value to the cell, it will only affect what is allowed to be entered.

Understood. However I am trying to apply a precondition so that if a condition is not met the cell populates with an automatic "N/A", if it is met then it looks to the Register to as a data validation list to find a unique reference number that has not already been applied. When I remove the above formula and insert it into the cell itself it works correctly.

I have inserted Column B helper to give true or false if a condition is met.

=IF(B2="True",'Disallowed Cost Register'!$B$7#)

As you say if the cell = "False" then the data validation does not apply.

The only other workaround, whilst not ideal would be to conditionally format the column / black out the text. Are there any potential workarounds without having to use a VBA?

 

[RoryA]

Understood. However I am trying to apply a precondition so that if a condition is not met the cell populates with an automatic "N/A"

Yes, but as I said, DV will not do that.

Realistically, I suspect you will need VBA, which is often the case with conditional data validation. For example, in this situation, if B2 were to change value, you would still have the old value in your DV cell, even though that is now invalid.