Two Way Conditional cells possibility?

[Renarian]

Macro Skill Sheet Test.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1			Anger
2			Total			Precise Hitter			Hard Precise Hitter			Stunning Strike
3			0			0			0			0
4			Hard Hitter			0	% Crit Chance		0	% Crit DMG		0	% Extra Stun
5			0			Piercing Expert			Poison Expert			Poison Mastery
6			0	Max Weapon DMG		0			0			0
7	Total Used					0	% Piercing DMG		0	% More Poison DMG		0	% Poison Crit Chance
8	0					Blunt Expert			Bleed Expert			Bleed Mastery
9						0			0			0
10	Level					0	% Blunt DMG		0	% Bleed Dump DMG		0	% Bleed Dump Crit Chance
11	0					Slashing Expert			Harder Hitter			Berserker
12						0			0			0
13						0	% Slashing DMG		0	Max Weapon DMG		0	% DMG [<25% HP]
Sheet1

Cell Formulas
Range	Formula
C3	C3	=C5+F3+F6+F9+I3+I6+I9+L3+L6+L9+F12+I12+L12
F4	F4	=(F3*0.3)
I4	I4	=(I3*2)
L4,C6	L4	=L3
F7,F13,F10	F7	=F6*3
I7	I7	=I6*8
L7,I13,L10	L7	=L6*2
A8	A8	=C3+C17+C31
I10	I10	=I9*6
A11	A11	=A8/4
L13	L13	=L12*5

Cells with Data Validation
Cell	Allow	Criteria
C5	Whole number	<=3
C2	Any value
I6	Custom	=AND(F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0)
F6	Custom	=AND(C5<>"",C5=3,F6<31,MOD(F6,1)=0)
L3	Custom	=AND(I3<>"",I3>7,L3<11,MOD(L3,1)=0)
I3	Custom	=AND(F3<>"",F3>7,I3<31,MOD(I3,1)=0)
F3	Custom	=AND(C5<>"",C5=3,F3<21,MOD(F3,1)=0)
L6	Custom	=AND(I6<>"",I6>9,L6<21,MOD(L6,1)=0)
L9	Custom	=AND(I9<>"",I9>9,L9<21,MOD(L9,1)=0)
A10	Any value
A7	Any value
I9	Custom	=AND(F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I9<16,MOD(I9,1)=0)
I12	Custom	=AND(F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I12<11,MOD(I12,1)=0)
F12	Custom	=AND(C5<>"",C5=3,F12<31,MOD(F12,1)=0)
F9	Custom	=AND(C5<>"",C5=3,F9<31,MOD(F9,1)=0)
L12	Custom	=AND(I12<>"",I12>4,L12<21,MOD(L12,1)=0)

Got a lot of help from Dreid1011 on my work project and finished that thanks to them! Now using what I learned from that I am trying to create this. I tried a few things but reverted to this again because it works mostly how I want it. What I mean to use as an example: if L6 has a value of at least 1 I don't want someone going back to I6 and changing to a lesser value than the condition value to allow enteries into L6. Is it possible to do this?

 

[awoohaw]

Once L6 is populated must the value in I6 also meet the conditional formatting rule that is already there?
This uses the current rule above if L6 is blank, then adds I6>=L6 when it is not blank.

=or(
AND(L6="",F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0),
=AND(I6>=L6,F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0))

 

[Renarian]

Once L6 is populated must the value in I6 also meet the conditional formatting rule that is already there?
This uses the current rule above if L6 is blank, then adds I6>=L6 when it is not blank.

=or(
AND(L6="",F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0),
=AND(I6>=L6,F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0))

Yes I would like all values to meet all criteria at all times.

 

[Renarian]

Woops was tired last night before I posted. I meant is it possible for something like this Cell C5:
=or(or(F3=0,F6=0,F9=0,F12=0),and(C5<=3,mod(C5,1)=0)
for F3:
=or(AND(C5<>"",C5=3,F3<21,MOD(F3,1)=0),and(I3=0,mod(f3,1)=0)
I am just throwing some things out as I am still learning all this. not sure if any of what I put is redundant or completely off base?

 

[Renarian]

or is
=or(
AND(L6="",F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0),
=AND(I6>=L6,F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0)) the only way to make what I wish possible?

There will be no blanks at all allowed in the sheet. All location for person data entry will have zero or a whole number smaller than a ceiling.

I have it working right going left to right, but I want to prevent users from going all the way right and then adding a false entry prior (to the left of it).

 

[Renarian]

I am a little confused by response (probably as much as you were about what I typed last night when I was tired.) Could you maybe elaborate on the formula you gave and explain what it does? I still am less that a novice when it comes to Excel.

 

[awoohaw]

I've been out running around today and also looking at other excel questions.
Let me try to explain the formula I have in Post #2:

=or(
AND(L6="",F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0),
=AND(I6>=L6,F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0))


Your new question was:
if L6 has a value of at least 1 I don't want someone going back to I6 and changing to a lesser value than the condition value to allow entries into L6. Is it possible to do this?

original rules:
I6 =AND(F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0)
For I6: Only format when sum of (F6, F9, F12) is not zero & all 3 are populated, I6 is less than 16, and I6 is a whole number

L6 =AND(I6<>"",I6>9,L6<21,MOD(L6,1)=0)
For L6: Only format when I6 is populated with a whole number gt 9 and L6 lt 21

So, my thought was that your new requirement "really" didn't make any difference of being at Least 1, but only that the value of I6 must be greater than or equal to I6.
So, I just added a second validation rule that is almost like the first for I6, instead of being sure L6 is blank, to also make sure I6 is greater than L6.

I hope that makes sense. And I"m not 100% sure it will catch all your scenarios. Please test it out.

 

[Renarian]

I've been out running around today and also looking at other excel questions.
Let me try to explain the formula I have in Post #2:

=or(AND(L6="",F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0),AND(I6>=L6,F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0))


Your new question was:
if L6 has a value of at least 1 I don't want someone going back to I6 and changing to a lesser value than the condition value to allow entries into L6. Is it possible to do this?

original rules:
I6 =AND(F6<>"",F9<>"",F12<>"",SUM(F6,F9,F12),I6<16,MOD(I6,1)=0)
For I6: Only format when sum of (F6, F9, F12) is not zero & all 3 are populated, I6 is less than 16, and I6 is a whole number

L6 =AND(I6<>"",I6>9,L6<21,MOD(L6,1)=0)
For L6: Only format when I6 is populated with a whole number gt 9 and L6 lt 21

So, my thought was that your new requirement "really" didn't make any difference of being at Least 1, but only that the value of I6 must be greater than or equal to I6.
So, I just added a second validation rule that is almost like the first for I6, instead of being sure L6 is blank, to also make sure I6 is greater than L6.

I hope that makes sense. And I"m not 100% sure it will catch all your scenarios. Please test it out.

Why is this part in there ?

L6=""

because it looks like this is looking for a blank cell. There are no blank cells as all are prefilled with 0 from the start but I might not be understanding something there.

I tried it out and it still allows changing if L6 is a value of 1-9 which very well could happen. I am looking to make it lock the cell even if it is 1-9. So would changing the detection of L6 be: L6<>0 or L6=0 to prevent changing once a higher value than 0 is in L6?

I6>=L6 I mean this section specifically

 

[awoohaw]

I am not sure I did it correctly then. I did not read any where (I could have missed it) that all of the cells had a value. But, as I look at it now I think my approach is wrong. Please "un mark" the solution I offered in Post 2.
I regret that. I hope others will pop onto the thread.

 

[Renarian]

I understand. I will also add that there is a macro button that adds 0 to all entry cells. It simply adds 0 from right to left to reset the cells. This is why there is never a blank cell in the sheet. This is why I was wondering about switching section I6>=L6 to:
L6=0 other possibility
L6<>0
to satisfy the condition of 0 always being there unless a user is able to enter a value into L6 in which case once a value is there I6 can no longer have data be input into it? Would either of those 2 solutions work better or am I way off base? I am going to try both myself after I post here. I am just going to change that section. I will post the results and if it works ask anyone to please cut out anything that is not necessary.