Hi folks,

I have a spreadsheet with the following kind of entries

A B C

Bob 1 0.5 0.5
John 2 0 0
Linda 0 1 1


I need to know how to restrict the maximum value that can be keyed under A,B
& C for each person, so that no cell can have an answer greater than 2, and
the sum total of A,B & C for each person cannot be any more than 2 either.

I'm pretty new to this, so I may be missing something blindingly simple.

Hi,
Go to Data validation, in Allow enter custom, in the formula enter A2:c4<3,
go to error alert and enter the message you want to display

I assume that the cell where you enter the information are from A2 to C4

"ScottishSteve" wrote:

Hi folks,

I have a spreadsheet with the following kind of entries

A B C

Bob 1 0.5 0.5
John 2 0 0
Linda 0 1 1


I need to know how to restrict the maximum value that can be keyed under A,B
& C for each person, so that no cell can have an answer greater than 2, and
the sum total of A,B & C for each person cannot be any more than 2 either.

I'm pretty new to this, so I may be missing something blindingly simple.

Hi Eduardo,

Thanks for your help!

Unfortunately that doesn't seem to be working. Also, I can't use the <3
entry, as that would allow 2.1, 2.1, 2.3 etc wouldn't it?

Does anyone have any ideas?

Thanks

Steve

"Eduardo" wrote:

Hi,
Go to Data validation, in Allow enter custom, in the formula enter A2:c4<3,
go to error alert and enter the message you want to display

I assume that the cell where you enter the information are from A2 to C4

"ScottishSteve" wrote:

Hi folks,

I have a spreadsheet with the following kind of entries

A B C

Bob 1 0.5 0.5
John 2 0 0
Linda 0 1 1


I need to know how to restrict the maximum value that can be keyed under A,B
& C for each person, so that no cell can have an answer greater than 2, and
the sum total of A,B & C for each person cannot be any more than 2 either.

I'm pretty new to this, so I may be missing something blindingly simple.

Try this:

Select the range for input. In Data Validation Settings tab Allow box,
select Decimal. In Data box, select "less than or equal to." In Maximum box,
type 2. Click Ok. (I'm using 2007. It's probably the same in 2003).



"ScottishSteve" wrote:

Hi Eduardo,

Thanks for your help!

Unfortunately that doesn't seem to be working. Also, I can't use the <3
entry, as that would allow 2.1, 2.1, 2.3 etc wouldn't it?

Does anyone have any ideas?

Thanks

Steve

"Eduardo" wrote:

Hi,
Go to Data validation, in Allow enter custom, in the formula enter A2:c4<3,
go to error alert and enter the message you want to display

I assume that the cell where you enter the information are from A2 to C4

"ScottishSteve" wrote:

Hi folks,

I have a spreadsheet with the following kind of entries

A B C

Bob 1 0.5 0.5
John 2 0 0
Linda 0 1 1


I need to know how to restrict the maximum value that can be keyed under A,B
& C for each person, so that no cell can have an answer greater than 2, and
the sum total of A,B & C for each person cannot be any more than 2 either.

I'm pretty new to this, so I may be missing something blindingly simple.