I've been trying for days to learn how to use formulas in a spreadsheet and have done pretty well, but now have one that I can't figure out. I want users of the spreadsheet not to be able to enter anything in a series of cells if a defining number is entered in a cell.
Example: User must enter a number (of participants) from 2 - 10 in $k4.
If 2 is entered in k4, then cells d9:j18 are both unable to accept data and the area is masked by a pattern.
If 3 is entered in k4, then cells d10:j18...
If 4..., then d11:18...
If 5..., then d12:18...
If 6..., then d13:18...
If 7..., then d14:18...
If 8..., then d15:18...
If 9..., then d16:18...
If 10..., then d17:18...
Further, user must also enter the total number (of questions) from 10 - 30 in $k5.
If user enters 10, then that's the maximum number that can be entered in e9:18 & g9:18
4 Number of participants: 2 (k4)
5 Number of questions: 10 (k5)
Cells:c_d__e__f____g__h____i___j__
09__1 __ _2_20%_10_100%_8_80%
10__2 __ _1_10%_10_100%_9_90%
11__3 __ _0__0%__0__0%__0__0%
12__4 __ _0__0%__0__0%__0__0%
13__5 __ _0__0%__0__0%__0__0%
14__6 __ _0__0%__0__0%__0__0%
15__7 __ _0__0%__0__0%__0__0%
16__8 __ _0__0%__0__0%__0__0%
17__9 __ _0__0%__0__0%__0__0%
18__10__ _0__0%__0__0%__0__0%
19__Av:_1.5_15%_10_100%_8.5_85%
I have e19 & g19 set up to total automatically, and f9:f18 & h9:f18 to automatically record the percentage when the score is entered in the e & g columns. Also i9:j18 also are automated.
Although the averages are set to average according to the # of participants [=SUM(E9:E18)/$K4], I can't figure out how to prevent the totals to not exceed 100%. That's why I wanted to block out those areas so they couldn't input a number that exceeds either/both the number of participants and/or the number of questions.
Sorry this is so long, but I've been for days experimenting, but nothing I do seems to work. I've tried formulas (incorrect, of course ), validation & conditional formatting.
I'd be so grateful if someone could help me. Thanks. [/b][/list]
Example: User must enter a number (of participants) from 2 - 10 in $k4.
If 2 is entered in k4, then cells d9:j18 are both unable to accept data and the area is masked by a pattern.
If 3 is entered in k4, then cells d10:j18...
If 4..., then d11:18...
If 5..., then d12:18...
If 6..., then d13:18...
If 7..., then d14:18...
If 8..., then d15:18...
If 9..., then d16:18...
If 10..., then d17:18...
Further, user must also enter the total number (of questions) from 10 - 30 in $k5.
If user enters 10, then that's the maximum number that can be entered in e9:18 & g9:18
4 Number of participants: 2 (k4)
5 Number of questions: 10 (k5)
Cells:c_d__e__f____g__h____i___j__
09__1 __ _2_20%_10_100%_8_80%
10__2 __ _1_10%_10_100%_9_90%
11__3 __ _0__0%__0__0%__0__0%
12__4 __ _0__0%__0__0%__0__0%
13__5 __ _0__0%__0__0%__0__0%
14__6 __ _0__0%__0__0%__0__0%
15__7 __ _0__0%__0__0%__0__0%
16__8 __ _0__0%__0__0%__0__0%
17__9 __ _0__0%__0__0%__0__0%
18__10__ _0__0%__0__0%__0__0%
19__Av:_1.5_15%_10_100%_8.5_85%
I have e19 & g19 set up to total automatically, and f9:f18 & h9:f18 to automatically record the percentage when the score is entered in the e & g columns. Also i9:j18 also are automated.
Although the averages are set to average according to the # of participants [=SUM(E9:E18)/$K4], I can't figure out how to prevent the totals to not exceed 100%. That's why I wanted to block out those areas so they couldn't input a number that exceeds either/both the number of participants and/or the number of questions.
Sorry this is so long, but I've been for days experimenting, but nothing I do seems to work. I've tried formulas (incorrect, of course ), validation & conditional formatting.
I'd be so grateful if someone could help me. Thanks. [/b][/list]