Data Validation formula

[Edgarvelez]

Hi All,
I am using data validation to protect cell D27 if cell D9 does not equal the word Metric Ton from it's drop-down and I have been able to accomplish by going to cell D27 and using Data Validation / Custom and the formula is =$D$9<>"Metric Ton" and I am able to protect cell D27

I would like to add to this formula the following =$D$10<>"GW" so that if D10 does not equal the word GW then it also protects D27
I am trying to protect the cell D27 if either or both conditions are met.
Attached is a jpg thanks in advanced.

 

[J.Ty.]

In this case, try
=NOT(OR(EXACT(D9,"Metric Ton"),EXACT(D10,"GW"),EXACT(D12,"Single Bundle Weight")))
because you need TRUE for the edit to be permitted and FALSE to be rejected.

J.Ty.

 

[Edgarvelez]

I just tried it and it is protecting only if all 3 are met.

 

[J.Ty.]

This should work:
=AND(NOT(EXACT(D9,"Metric Ton")),NOT(EXACT(D10,"GW")),NOT(EXACT(D12,"Single Bundle Weight")))

However, it is something extremely weird. This formula is exactly equivalent to the previous one, you can enter them in a spreadsheet in two adjacent cells and watch what values they produce for various values of D9, D10 and D12. They are always equal. However, if I put them in DV, they behave differently. Seems like a bug in Excel, unless there is something into it I do not know about.

J.Ty.

 

[Edgarvelez]

I think its a bug

 

[Edgarvelez]

thanks I will figure this out one way or another, I will let you know what I find out.

Thanks for your help

 

[J.Ty.]

OK, I more or less understand now. Open your data validation dialog and uncheck "Ignore blank" box.

It is a very strange one: it applies to the arguments of the formula, and not the field being protected. So when checked, it simpy permits whatever you enter there, if any of the argument fields (D9, D10 or D12) is blank.

Then either of my formulas should work as intended. But please check it carefully!

J.Ty.

 

[Edgarvelez]

I tested it and it worked, You The Master.
Awesome and thank you sooooo much.

 

[J.Ty.]

Thanks for your kind words and good luck!

J.Ty.