only allow one cell inputed for a range of cells

[Guest]

I am making a spread sheet to be used for data entry. the data entered is an
"x" with in a column. But I only want to allow one cell from f13:j13 to be
entered
Ex f13 is unsat g13 in below ave h13 is average and so on so by puting an
"x" in f13 it totals down and I don't want a double entry within the range of
f13:j13

 

[Guest]

Hi Brian,

Try Data Validation.

Select F13:J13

xl2007 version: Data tab on ribbon->Data tools block->Data Validation

pre xl2007 versions: Menu item Data->Validation->

In allow field: Select Custom

Uncheck Ignore blanks

In Formula field: =COUNTA($F$13:$J$13)<2

This validation does not confirm that an X has been entered but it only
allows one of the cells to have an entry.
Regards,

OssieMac

 

[Guest]

Hi Brian,

Did a bit more work on your request and the following will validate for only
one X in the range and the user can only enter X or blank. However, it is not
case sensitive and x and X are valid entries.

Select range of cells for Conditional format (F13:J13)

Select Data validation as follows:-

xl2007 version: Ribbon tab Data->Data Validation (In Data Tools block)

Pre xl2007 versions: Menu item Data->Validation.

In the Data Validation dialog box:-
Allow field: Select Custom
Ignore blank: Uncheck
Formula field: =AND(COUNTA($F$13:$J$13)<2,OR(F13="X",F13=""))

The following is optional:-
Select Input Message tab
Input Message field: Enter X in one cell only for range G13:J13
Select Error Alert tab
Error Message field: Only X is valid and only in one field in range G13:J13

Click OK to exit.

Some notes on entering validation formulas:-

Formula must evaluate to true to permit entry of data.

Validation Formula is entered as if entering the formula for one cell only
(the Active cell of the selected range.). The Active cell of the range is the
one which is bound by the thick border but the interior is not highlighted
like the rest.

The range reference used in the above formula like G13:J13 above needs to be
entered in absolute format. (That is $G$13:$J$13) otherwise as Excel copies
them to the adjacent cells, the reference will change to H13:K13, I13:L13
etc. (However, depending on the actual validation formula, it might be that a
range is meant to change such as testing the previous 4 cells progressively
across the page.)

Testing the value of individual cells across the range like in the above
formula where it is testing if the cell is X or blank, the cell reference is
in G13 format so that it DOES CHANGE to H13, I13 etc as Excel copies it
across the range.

Regards,

OssieMac

 

[Guest]

Thanks alot. That hit the nail on the head. I've been racking my brains.

 

[Guest]

one more thing off this subject. I've got calculations adding up different
cells on the page giving a final grade. But I need the grade to be no lower
than 70.
here is my cell formula.
=IF(SUM(F19,Q19,F38,Q38,(F49:J49))>0,SUM(F19,Q19,F38,Q38,(F49:J49)),(""))

 

[Guest]

Hi Brian,

Pleased that I have helped so far and will try to help with you new question.

I see that you only want to sum it if it is greater than zero. However, what
do you want to do if the sum is less than 70. Like if it adds up to 68 do you
want to insert 70 instead?

I am going to bed now so will look at your reply in the morning.

Regards,

OssieMac

 

[Guest]

If the sum is less than 70 I would like it to still show 70. In the big
picture with grade averages I can only allow the lowest grade to be a 70.
Thanks again.

 

[Peo Sjoblom]

=MAX(70,SUM(.......))

will return 70 if the sum is less than 70


--


Regards,


Peo Sjoblom

 

[Guest]

Hi again Brian,

Looks like Peo's answer is what you need. However, just as a little more
information, you can dispense with some of the brackets in your formula.

With Peo's suggestion included your formula should be:-

=IF(SUM(F19,Q19,F38,Q38,F49:J49)>0,MAX(70,SUM(F19,Q19,F38,Q38,F49:J49)),"")

Note that the above is actually one line but has broken during posting.

Regards,

OssieMac