Data Validation

[Renarian]

Now pleasea bare with me. I have a lot of information that I need to impart. Let me start by saying I am a complete novice in the use of Excel. I have no idea of a lot of terms and function interactions. If clarification is needed I will provide it the best that I can. I have prepared 2 dummy samples for trying to preset clarification on the problem I need solved if possible. I do not need specifics for each just how the sample would be solved according to my needs. When I mention a cell is capped I mean the value entered by user can not exceed this value (cell does not accept entries above.) All the numbered areas here are user input cells. All of these need to always be whole integers of varying values less than a number and 0 counts in each (the actual project has too many different ceiling cells to list so I will figure those out once I get a generic starter.) No blank spaces text of any kind should be allowed in the input cells.

To start offf with:

The values I give here are arbitray and the image values do not matter. I need each row to be linked together in such a way that without the input value being higher than a value. Ex: A1 can not be higher than 3 but in order for entries to be made in C1, A1 has to be greater than 2 and C1 is capped at 20. Next C1 need to be greater than 6 to allow entried into E1 and capped at 10. Then in G1 lets say G1 can not be greater than 30 and E1 needs be greater than 4 for entried to be made in G1. Complete the other two rows of chains similarly to row 1 because all 3 rows are just what I call chains until Column G. In column G all 3 combine into last entry cell I3. In order for I3 to allow data it needs to check G1, G3 and G5 to make sure 1 or a multiple of the 3 cells is greater than 14 and it is capped at 10. This is the bulkier part of my problem.

Next:

Some chains come to a point like this where 2 merge into 1. The values entered in the image do not matter To start I will say both K2 and K4 can not have a vulue higher than 3. Cell M2 can not be higher than 10. Cells M4 and O3 can not exceed 1. again these are chains dependant on lower alphabet values to allow entry into the cells. My biggest concern is M2 and M4 must have a value of at least 1 in both or either (M1 is restricted to values of 0 and 1) in order to allow entries into O3. This concludes the second part.

The ultimate coal of the project is to allow mutltiple users to access this so of course I want to protect as much of this as I can. How can I ensure that any of the data entry cells are still usable by anyone but protect names/headings/formulas or any formatting/possible data validation is not compromised?

I know this is a doozy and I have been working on this for months now. I have asked in many different forums or help guides. As I said I am a complete Novice (well maybe a Novice is more of an upgrade) and everything I have learned about Excel has been self taught. I have fiddled around with conditional formatting and data validation a lot but not seeming to get the results I desire. I know course on Excel would be helpful but let's just leave it at I was the only one who even heard of Excel at the meeting. Please if any of this is not making sense let me know. I can not divulge the actual specifics of the project because it is confidential but it is my hope that the examples I have provided are close enough so that I am able to get a working step in the right direction.

Sincerely
Foot in Mouth

 

[dreid1011]

View attachment 99271
I added this note here. O3 checks cells M2 AND M4 to see if Either of them has a positive integer value is not 0. So for example M4 is either a 0 or a 1 and M2 can be 1 through 10. Now if either of those has a 1 then O3 opens up. If M2 is 8 and M4 is 0 then O3 opens up. If both are 0 then O3 can not have any values added.

Yes, I saw that. What I need to know is how does K2/K4 restrict M2/M4? Is it as simple as K2/K4 must have a positive value greater than 0 before a value can be entered into M2/M4?

Also quickly wanted to ask is there a way to restrict all input cells to whole number values to be entered? Even cells like C1, E1, G1?

Possibly, I will look into that.

 

[Renarian]

K2 restricts M2 by needing a value greater than or equal to 3
K4 restricts M4 by needing a value greater than or equal to 5

 

[dreid1011]

The values entered in the image do not matter To start I will say both K2 and K4 can not have a vulue higher than 3. Cell M2 can not be higher than 10. Cells M4 and O3 can not exceed 1.

K2 restricts M2 by needing a value greater than or equal to 3
K4 restricts M4 by needing a value greater than or equal to 5

Originally, you said K2 and K4 could not be greater than 3, but now they have to be >= 3 and 5 respectively? If I understand correctly.

 

[dreid1011]

See if this works for you. If the values for K2/K4 are not accurate, adjust them in the data validation to suit your needs. And don't mind the extra text in the cells, I was reminding myself of the limits.

Book1 9-25-2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1		3
2	A1<=3		C1<=20		E1<=10		G1<=30						5
3											K2<=3		M2<=10
4							SUM > 14								O3<=1
5											K4<=3		M4<=1
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A1	Custom	=AND(A1<=3,MOD(A1,1)=0)
C1	Custom	=AND(A1<>"",A1>=2,C1<=20,MOD(C1,1)=0)
E1	Custom	=AND(C1<>"",C1>6,E1<=10,MOD(E1,1)=0)
G1	Custom	=AND(E1<>"",E1>4,G1<=30,MOD(G1,1)=0)
I3	Custom	=AND(SUM(G1,G3,G5)>14,I3<=10)
K4	Custom	=AND(K4<=3,MOD(K4,1)=0)
O3	Custom	=AND(OR(M2>=1,M4>=1),O3<=1,MOD(O3,1)=0)
M2	Custom	=AND(K2<>"",K2>=3,M2<=10,MOD(M2,1)=0)
M4	Custom	=AND(K4<>"",K4>=3,M4<=10,MOD(M4,1)=0)
K2	Custom	=AND(K2<=3,MOD(K2,1)=0)

 

[Renarian]

See if this works for you. If the values for K2/K4 are not accurate, adjust them in the data validation to suit your needs. And don't mind the extra text in the cells, I was reminding myself of the limits.

Book1 9-25-2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	1		3
2	A1<=3		C1<=20		E1<=10		G1<=30						5
3											K2<=3		M2<=10
4							SUM > 14								O3<=1
5											K4<=3		M4<=1
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A1	Custom	=AND(A1<=3,MOD(A1,1)=0)
C1	Custom	=AND(A1<>"",A1>=2,C1<=20,MOD(C1,1)=0)
E1	Custom	=AND(C1<>"",C1>6,E1<=10,MOD(E1,1)=0)
G1	Custom	=AND(E1<>"",E1>4,G1<=30,MOD(G1,1)=0)
I3	Custom	=AND(SUM(G1,G3,G5)>14,I3<=10)
K4	Custom	=AND(K4<=3,MOD(K4,1)=0)
O3	Custom	=AND(OR(M2>=1,M4>=1),O3<=1,MOD(O3,1)=0)
M2	Custom	=AND(K2<>"",K2>=3,M2<=10,MOD(M2,1)=0)
M4	Custom	=AND(K4<>"",K4>=3,M4<=10,MOD(M4,1)=0)
K2	Custom	=AND(K2<=3,MOD(K2,1)=0)

So with this that completes those two parts. Any luck on finding a way to restrict all cells to a positive whole number including zero? Like another cell to verify (true or false return) if all the input cells are whole numbers then have each cell also check if that verification cell comes back as true?

 

[Renarian]

Thank you for this by the way! Immensely helpful and is allowing me to see and learn more about Excel! As soon as this last bit is confirmed as either possible or not then I will mark as solved (I just need to see where to do that)

 

[dreid1011]

Thank you for this by the way! Immensely helpful and is allowing me to see and learn more about Excel! As soon as this last bit is confirmed as either possible or not then I will mark as solved (I just need to see where to do that)

The MOD(cell,1)=0 at the end of most of those formulas is making sure they are whole numbers. Adding in a condition to make them positive is simple.

 

[dreid1011]

Book1 9-25-2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	2		19		7		13
2	A1<=3		C1<=20		E1<=10		G1<=30				3		6
3							2		10		K2<=3		M2<=10		1
4							SUM > 14				3		0		O3<=1
5											K4<=3		M4<=1
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A1	Custom	=AND(A1>=0,A1<=3,MOD(A1,1)=0)
C1	Custom	=AND(A1<>"",A1>=2,C1>=0,C1<=20,MOD(C1,1)=0)
E1	Custom	=AND(C1<>"",C1>6,E1>=0,E1<=10,MOD(E1,1)=0)
G1	Custom	=AND(E1<>"",E1>4,G1>=0,G1<=30,MOD(G1,1)=0)
I3	Custom	=AND(SUM(G1,G3,G5)>14,I3>=0,I3<=10,MOD(I3,1)=0)
K4	Custom	=AND(K4>=0,K4<=3,MOD(K4,1)=0)
O3	Custom	=AND(OR(M2>=1,M4>=1),O3>=0,O3<=1,MOD(O3,1)=0)
M2	Custom	=AND(K2<>"",K2>=3,M2>=0,M2<=10,MOD(M2,1)=0)
M4	Custom	=AND(K4<>"",K4>=3,M4>=0,M4<=10,MOD(M4,1)=0)
K2	Custom	=AND(K2>=0,K2<=3,MOD(K2,1)=0)

 

[Renarian]

Book1 9-25-2023.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	2		19		7		13
2	A1<=3		C1<=20		E1<=10		G1<=30				3		6
3							2		10		K2<=3		M2<=10		1
4							SUM > 14				3		0		O3<=1
5											K4<=3		M4<=1
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A1	Custom	=AND(A1>=0,A1<=3,MOD(A1,1)=0)
C1	Custom	=AND(A1<>"",A1>=2,C1>=0,C1<=20,MOD(C1,1)=0)
E1	Custom	=AND(C1<>"",C1>6,E1>=0,E1<=10,MOD(E1,1)=0)
G1	Custom	=AND(E1<>"",E1>4,G1>=0,G1<=30,MOD(G1,1)=0)
I3	Custom	=AND(SUM(G1,G3,G5)>14,I3>=0,I3<=10,MOD(I3,1)=0)
K4	Custom	=AND(K4>=0,K4<=3,MOD(K4,1)=0)
O3	Custom	=AND(OR(M2>=1,M4>=1),O3>=0,O3<=1,MOD(O3,1)=0)
M2	Custom	=AND(K2<>"",K2>=3,M2>=0,M2<=10,MOD(M2,1)=0)
M4	Custom	=AND(K4<>"",K4>=3,M4>=0,M4<=10,MOD(M4,1)=0)
K2	Custom	=AND(K2>=0,K2<=3,MOD(K2,1)=0)

what does the double quotation mark do in the formula?

 

[dreid1011]

what does the double quotation mark do in the formula?

Makes sure the cell is not blank. Without it, you could enter a further value without the previous values filled in. With that being said, it only works if the "Ignore Blanks" option is unchecked for each data validation.

IE: A1 is left blank, you could enter any value you want into C1 and it would not check data validation for C1 limits.