Data Validation with IF/OR formula

[BoilerMan691]

Hello,

I am trying to write a Data Validation formula where if C7=N then D7 MUST =N, but if C7=Y then D7 can =Y or =N. Any help would be much appreciated!

Thanks,
James

 

[pjmorris]

In D7 the validation is =IF(C7="N", D7="N",TRUE)

However, if you subsequently change C7 the validation won't be tested. I would suggest using conditional formatting to catch that situation.

HTH

 

[BoilerMan691]

T

In D7 the validation is =IF(C7="N", D7="N",TRUE)

However, if you subsequently change C7 the validation won't be tested. I would suggest using conditional formatting to catch that situation.

HTH

Thanks PJ,

So I tried that, but then it allowed me to use other characters besides Y when I made C7=Y. I'm trying to make it hard and fast where the only options for data entry in columns C and D are Y or N. And if column C data is N then column D data MUST be N also, however if column C data is Y then column D data may be Y or N. I'm already using conditional formatting to highlight columns B C and D Yellow or Green depending if it's a Y/N or Y/Y and if it's N/N then the column stays without highlight. Hope that better explains what I'm after?

Best
James

 

[Snakehips]

@BoilerMan691 Does this help?

Picking Teams.xlsm
	C	D
7	N	N
Sheet6

Cells with Data Validation
Cell	Allow	Criteria
D7	Custom	=OR(D7=C7,D7="N")

 

[pjmorris]

Try this: =IF(C7="N", D7="N", or(D7="Y", D7="N")) which should solve the problem.

 

[BoilerMan691]

PJ and Snake,

Thank you both for the suggestions. Both formulas appear to work the same as one another. Is there a way to incorporate the UPPER formula into these? I've tried nesting it as well as using & but neither have been successful. Sorry if my questions seem low level, but I'm a boiler mechanic by trade turned manager and trying to improve efficiencies on certain shop processes. I appreciate all of the help you've both offered me so far.

Thanks,
James

 

[Snakehips]

James,
Then maybe try using validation lists like below?
Assumes that input to C7 will be restricted to either Y or N ?
Limit entry into C7 by way of list.
Create a two rows, somewhere out of the way, as per H2:H3 in the example.
Reference H2:H3 as the source of validation list for D7
Tick to retain dropdown DV list or not.

Picking%20Teams (version 1).xlsb
	C	D	E	F	G	H
1						Val List
2						N
3						Y
4
5
6
7	Y	N
8
Sheet6

Cell Formulas
Range	Formula
H2	H2	=IF(OR(C7="N",C7="Y"),"N","")
H3	H3	=IF(C7="Y","Y","")

Cells with Data Validation
Cell	Allow	Criteria
C7	List	n,y,N,Y
D7	List	=$H$2:$H$3

HTH

 

[pjmorris]

replace C7 with UPPER(C7) and D7 with UPPER(D7) in either of our formulas. I actually prefer Snakehips formula to my own as its 'slicker'!

HTH

 

[Snakehips]

James, If it were me I think I would see the List option on both cells as the way to go. It prevents adding other letters to D7 if C7 is empty.
However, I do see that I carelessly included lowercase n & y in the list for C7. Remove them and you should be good and if you enter a lowercase in cell it should auto capitalise.

 

[BoilerMan691]

PJ and Snake,

I just wanted to write back and thank both of you for all of your help. I wound up using the validation list route suggested by Snake. I also used the conditional formatting to highlight any changes that may have been made after the initial validation check passes suggested by PJ. I've been working on this spreadsheet for a few weeks now making small tweaks to improve it to a point where it's "idiot proof".

Best,
James