Help: block group of cells when number is entered in a cell

[esfinc]

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]

 

[DRJ]

This will restrict input. Select D9:J18 and goto Data | Validation

Put in this formula

=($K$4+7<ROW())

Then goto Format | Conditional Formatting and input the same formula and select your formatting/shading.

 

[esfinc]

Re: Help: block group of cells when number is entered in a c

Thank you so much. The formulas worked almost perfectly. As given, it masked row 2. A given: there must be data in the first 2 rows. I changed the conditional formating putting 8 instead of 7. this masked the correct rows and changed to unmask rows as I entered higher numbers (up to 10) in the subsequent rows.

However, I am still able to enter data in the 1st row of the masked rows even when I replace the 7 with an 8 (or 6) into the validation formula.

Since I don't have any training in Excel, everything I do is intuitive. Forgive my ignorance.

Thanks again. [/u]

 

[HalfAce]

Re: Help: block group of cells when number is entered in a c

Hi esfinc,
Using Jacob's formula and adding a -1 at the end should work on the first line of your range. ie. =($K$4+7<ROW()-1)

This help?
Dan

Edit: The formula is not posting completely, so let me try & explain.
Put a -1 between the two )) at the end.

 

[DRJ]

Try

=$K$4+8>ROW()

For the Validation.

 

[DRJ]

Re: Help: block group of cells when number is entered in a c

Hi esfinc,
Using Jacob's formula and adding a -1 at the end should work on the first line of your range. ie. =($K$4+7<ROW()-1)

This help?
Dan

Edit: The formula is not posting completely, so let me try & explain.
Put a -1 between the two )) at the end.

I was having the same problem. I guess the less than sign is read as an openning html tag. You can post it properly if you use code tags.

code

message

/code

and put [] around those tags.

 

[esfinc]

Re: Help: block group of cells when number is entered in a c

Thanks so much, Jacob and Dan. I tried all your recommended ways, but still was not able to get that first row to not accept data. Since the mask worked perfectly, I took the easy way out and inserted a note telling users not to put data in masked cells. It's already taken hours for me to try various things, so I thought I had to wind it up.

I again thank you both for taking your valuable time to lend me a hand. And I've learned a great deal about using formulas that I hadn't known before.

Many blessings, Mary
Tampa