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.]

It seems that the formula you need is
=AND($D$9<>"Metric Ton",$D$10<>"GW")
Note also that <> does not respect case in strings, so your cell is also protected when "metric ton" or "gW" ar entered. DO you want to keep it as is?

J.Ty.

 

[Edgarvelez]

I will give the formula a try

 

[Edgarvelez]

Awsome it worked and thank you so much.
I changed <> to = and changed the formula around a bit to =AND($D$9="Metric Ton",$D$10="GW")
Will = respect case?

 

[J.Ty.]

To explain things:
Formula in Data Validation permits when it evaluates to ture, and blocks when it evaluates to false.
So

1. My variant =AND($D$9<>"Metric Ton",$D$10<>"GW") permits when both D9 is not "Metric ton" and D10 is not "GW" (or case variants thereof) and blocks in all other cases
2. Your variant=AND($D$9="Metric Ton",$D$10="GW") permits when D9 is "Metric ton" and D10 is "GW" (or case variants thereof) and blocks in all other cases

It means that changing <> to = completely changes the meaning of the formula (and does not change if it respects case).

The case sensitive variant of my formula is
=AND(NOT(EXACT($D$9,"Metric Ton")),NOT(EXACT($D$10,"GW")))

EXACT is an Excel function which checks if its arguments are exactly identical texts, including case.

 

[Edgarvelez]

Wow!!! This really works =AND(NOT(EXACT($D$9,"Metric Ton")),NOT(EXACT($D$10,"GW")))
Thank you soo much.

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.

 

[Edgarvelez]

Just Curious, is there a way to do the same based on the cell fill color?
I would like to protect any cell that is Gray at any time.

 

[J.Ty.]

In principle, formulas do not have access to colors fonts, etc., but:

• If your colors result from conditional formatting, you can use the formulas of conditional formatting in data validation to get the effect you want.
• If your colors do not change at all, you can protect the gray cells completely using protection mechanism, as opposed to data validation.
• In VBA you can do pretty much everything, even if it is not advisable.

J.Ty.

 

[Edgarvelez]

Hi J.Ty.
Unfortunately, my work got changed and I have to do things differently using your formula
=AND(NOT(EXACT($D$9,"Metric Ton")),NOT(EXACT($D$10,"GW")))

I found this on the web and it is exactly what I need to do now

=IF(OR(A5="Red",B5="Green"),TRUE,FALSE)

IF A5 (“Blue”) equals “Red”, OR B5 (“Green”) equals “Green” then return TRUE, otherwise return FALSE. In this case, the second argument is True, so the formula returns TRUE.

So with your formula, if D9="Metric Ton" OR D10="GW" so if any of these 2 arguments is true it will protect the cell.
I will have another scenario as follows:
D9="Metric Ton" OR D10="GW" OR D12="Single Bundle Weight" so if any of these 3 arguments is true it will protect the cell.

I tried using =IF(OR in data validation but could not get it to work.