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

esfinc

New Member
Joined
Mar 23, 2004
Messages
3
:rolleyes: 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 :oops: for days experimenting, but nothing I do seems to work. I've tried formulas (incorrect, of course :LOL:), validation & conditional formatting.

I'd be so grateful if someone could help me. Thanks. :confused: [/b][/list]
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
This will restrict input. Select D9:J18 and goto Data | Validation

Put in this formula

Code:
=($K$4+7<ROW())
Then goto Format | Conditional Formatting and input the same formula and select your formatting/shading.
 

esfinc

New Member
Joined
Mar 23, 2004
Messages
3
Re: Help: block group of cells when number is entered in a c

Thank you so much. The formulas worked almost perfectly. :biggrin: 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. :oops:

Thanks again. :)[/u]
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853

ADVERTISEMENT

Try
Code:
=$K$4+8>ROW()

For the Validation.
 

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Re: Help: block group of cells when number is entered in a c

HalfAce said:
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

New Member
Joined
Mar 23, 2004
Messages
3
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,784
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top