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

 

[Renarian]

I have also tried fiddling around with making tables to no success. For using Data Validation dropdown boxes in the user input field. Best I can do right now is typing the dropdown list in the source area by comma serparator.

 

[Renarian]

My biggest concern here is this will fall by the wayside as it has on other forums as I see there are a few views and no reply (even if at first glance this seems impossible to do that would help me out tremendously as well). If this is all gibberish then by all means say so and I will do my best to clarify, or tell me where it is unclear. If it is a matter of "needing to see the project to provide specific solutions" then let me know that too and I will see if I can get something lined up that does not give out private information.

Thanks.

 

[awoohaw]

I just saw this. So, let me just say that from my perspective (and I have the reading comprehension of a 10th grader, but pretty decent excel skills) your explanation of what you want is very "wordy". It may be as concise as I could describe your problem, so I am not criticizing but observing. This may be why you have no responses. Another reason is that you have posted a picture expecting the forum to recreate your scenario when they don't quite understand what you've written so they do want to waste time on a solution that is wrong because of incorrect assumption. To that end Mr. Excel has a tool called xl2bb add in that allows you to post mini worksheets of your work. This will include condtional formatting and named ranges, it isn't very good with charts and tables. If you can use that I think most forum users respond more quickly. If that isn't workable for you, then post your data as table, just copy and paste it into a comment. Also providing expected results and rules for conditional formatting would be very helpful. Help the forum help you.

I'll try to look at your posts and post a suggested solution in a little while. But, try to post something that the forum can copy.

Thanks in advance.

 

[Renarian]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	0		0		0		0	The 3 cells need to be greater than or equal to 15 to enter data into cell I3		Note				M2 or M4 needs to not be 0 for data entry in O3.
2											0	Restrict M2 by K2 and M4 by K4	0				Big Note 1
3	0		0		0		0		0	Note					0		Big Note 2
4											0		0				Big Note 3
5	0		0		0		0
6
7		Note
8		Column1		Column1		Column1		Column1				Column1
9		0		0		0		0				0
10		1		1		1		1				1
11		2		2		2		2
12		3		3		3		3
13				4		4		4
14				5		5		5
15						6		6
16						7		7
17						8		8
18						9		9
19						10		10
20								11
21								12
22								13
23								14
24								15
25
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
Q2:R2	Any value
Q3:R3	Any value
Q4:R4	Any value
O3	List	=$L$9:$L$10
K4	List	=$H$9:$H$24
N1:N6	Any value
M4	List	=$L$9:$L$10
M2	List	=$F$9:$F$19
G1	List	=$H$9:$H$24
E1	List	=$F$9:$F$19
C1	List	=$D$9:$D$14
J1	Any value
B7	Any value
H1:H6	Any value
I3	List	=$F$9:$F$19
J3	Text length	=4
A1	List	=$B$9:$B$12
A3	List	0, 1, 2, 3
E3	Custom	=C3>2
C3	Custom	=A3>2

I hope this is what you meant? If not let me know

 

[dreid1011]

How about this? I didn't do all the cells because the first row should give you what you need to finish the rest of them. The only thing I will note is that if you get to a further step, there is nothing stopping you or someone else from changing a previous value to something that would not have passed validation.

Book1
	A	B	C	D	E	F	G	H	I
1	3		20		9		7
2
3							8		5
4
5
Sheet2

Cells with Data Validation
Cell	Allow	Criteria
A1	Whole number	<=3
C1	Custom	=AND(A1>=2,C1<=20)
E1	Custom	=AND(C1>6,E1<=10)
G1	Custom	=AND(E1>4,G1<=30)
I3	Custom	=AND(SUM(G1,G3,G5)>14,I3<=10)

 

[Renarian]

That works wonders for the first part! Thanks a million

Now the second part at K, M & O. it is slightly different. that connection ends where two or more lines end with a check to see if there is a value in at least one of the boxes that is at least 1.

 

[dreid1011]

That works wonders for the first part! Thanks a million

Now the second part at K, M & O. it is slightly different. that connection ends where two or more lines end with a check to see if there is a value in at least one of the boxes that is at least 1.

Please explain how K2/K4 restrict M2/M4. I did not see how they relate in your prior description.

 

[Renarian]

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.

 

[Renarian]

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?