MrExcel Publishing
Your One Stop for Excel Tips & Solutions

data validation


Posted by Jimmy on December 27, 2000 11:43 AM

I need to be able to sum four cells, say A10, B10, C10 and D10 then in E10 use data validation or something else to sum these four cells and then alert the users that the sum does not equal 50 or 0. The total must equal either 50 or 0 or they get a message stating the need to redo the figures. Just for information the four cells A1:D1 contain countif statements counting the number of "x"'s in the cells above them.

Thanks


Posted by Tim Francis-Wright on December 27, 2000 12:32 PM


You could use data validation here, I suppose,
but a regular IF expression should work in E10:
=IF(OR(SUM(A12:D12)=20,SUM(A12:D12)=0),"OK","You must redo the figures!")


Posted by Jimmy on December 27, 2000 12:45 PM

Tim thanks for the reply, but I would like to be able to have the Error message pop up like it does in Data validation. Also I would like to hide the cell which contains the validation since the user will not need this cell just a message to let them know that they have to recheck there figures

Posted by Aladin Akyurek on December 27, 2000 1:26 PM

Can you clarify which figures the user must redo in case E10 is not equal to either 50 or 0?

Aladin

Posted by Tim Francis-Wright on December 27, 2000 1:30 PM

You could use data validation on the cells that
will have the x's to warn the user that there
are the wrong number:

Select the range in question.
Use Data/Validation from the worksheet menu bar.
Under Settings, choose Custom, then Formula
=and(SUM($A$10:$D$10)<>0,SUM($A$10:$D$10)<>50)
Under Error Alert, select Information or Warning,
and put your message there.

Unfortunately, this will not recognize deletions
and will bug the user every time she inputs an
x on the way to 50.

You might want to use a couple of Sheet Event
subs. They assume that the X's are in a range
called Data on Sheet1. Put these in the code
for Sheet1 in the VB Editor.

Private Sub Worksheet_Deactivate()
Dim Sigma As Double
Sigma = Application.WorksheetFunction.Sum(Range("A10:D10"))
If Sigma <> 0 And Sigma <> 50 Then ' send user back to the top left of the data
MsgBox "Must redo data!"
Sheets("Sheet1").Activate
Range("Data").Cells(1).Select
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Dim Sigma As Double
If Intersect(Target, Range("Data")) Is Nothing Then ' don't trigger if within the data range
Sigma = Application.WorksheetFunction.Sum(Range("A10:D10"))
If Sigma <> 0 And Sigma <> 50 Then
MsgBox "Must redo data!"
Range("Data").Cells(1).Select ' send user back to the top left of the data
End If
End If
End Sub


Good luck!

Posted by Jimmy on December 27, 2000 2:05 PM

Aladin, I was trying to simplfie things alittle, but probably just confuced things. What I have is four colums with about 65 rows in which the user must input an "x" under a certain column (this is an performance eval)the "x"s go under column four columns marked U N M E, I know have data validation in these user filled cells which prevent them from placing more tht one x in a column or more that one x in that row. At the bottom of each column I have a coutif statement that counts the "x"s, then in the next row a constant number that the number of "x"s is multiplied by, then in the next row the actual product of the constant x number of "x"s. The sum of the of these products are then divided by another constant number (200 in this case). Problem 1 - is the user cannot use more than or less than 50 "x"'s to complete the form or the formulas will not work properly. (exception if they want to print out a blank form it must except 0 "x"'s.
Problem 2 - There is section which applies to only supervisors and section that applies to only employees which enables the users to select more than 50 if he/she is not paying attention to the form. Hope this is not more confusing than when I started. Evertyhing works fine as is, but would like to limit the "x"s is possible, if not I can live with it.

Posted by Jimmy on December 27, 2000 2:08 PM

Tim


Tim look at my post above to see if this clarifies anything for you. I am not familar with the VB, so I will play around with the code you sent to see if it will work. Again thanks for your help.

Posted by Aladin Akyurek on December 28, 2000 8:18 AM

: I need to be able to sum four cells, say A10, B10, C10 and D10 then in E10 use data validation or something else to sum these four cells and then alert the users that the sum does not equal 50 or 0. The total must equal either 50 or 0 or they get a message stating the need to redo the figures. Just for information the four cells A1:D1 contain countif statements counting the number of "x"'s in the cells above them.

I don't think I really understand this 0 and/or 50 question. I hope the following would help you some way.

Select the range in which the user enters the X's and name it Answers via the Name Box.

Put the formula

=IF(COUNTA(Answers)<=10,"Yes","No")

in a cell on the same or preferably a separate sheet. Name this cell

NanswersOK?

via the Name Box. Then clear the range Answers and go to the first cell where you activate Data Validation. Choose custum (say the cell address is B4) and enter the formula (combining Tim's suggestions)

=AND(B4="X",COUNTIF($B4:$E4,"X")=1,NanswersOK?="Yes")

Copy this cell down and across.

Hope it works.

Aladin