Data Validation with a formula
Straigtforward question here....I know how to use data validation and I know how to use formulas. From what I understand data validation will only work with data directly entered into the cell. Is there anyway to get data validation to work with the result of a formula? For example I want a cell to record the number of times the number "1" is entered into a range. I'll use the countif function in the cell specifying the range and the criteria (being the number "1"). I then add the data validation to that cell wanting it to give me an error message when the count exceeds (say 5) in that cell. When the formula result exceeds 5 nothing happens. Is there anyway to get around this or will I need to settle for color coding the cell or something like that?
Thanks for your time.
Greg
Custom Data Validation
Instead of using Countif() it may be better to use Sumif()
So if the sum of the ones are > to 10 then data validation kicks in. In data validation,
in the allow box select "Custom"
Then in the source box enter the formula =SUMIF(D:D,1,D:D)<=10
This will stop you from having more than 10 1's
Data Validation with a formula
Davesexcel,
That works perfectly. I'm validating the section of the sheet that I am entering the data instead of the formula result box (if I'm figuring right). I tried it and it works perfectly. However I have another question now...using the Data Validation I can restrict how many times 1's are entered into that column. Is there anyway to get the validation to meet multiple criteria? I need the same critera met (let's say limiting the entry of digits 1 through 9 in a certain column to a maximum of 32 and 0's to a maximum of 128) I tried nesting the sumif function in the formula box and no go.....
Worksheet_Change to validate Column
Most likely you cannot have more tham 1 data validation rule in the same range.
We can use the, Worksheet_Change event.
Whenever you make a change in Column D. The code first counts the 0's, if there are more than 128 a msgbox will pop up.
The code then counts each number from 1 to 9, if there are more than 32, the msgbox pops up telling you.
Right click on the sheet tab and select view code, copy and paste this code there.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Or Target.Count > 1 Then Exit Sub
Dim x As Variant, Cnt As Integer, CntZr
Dim Msg1 As String, Msg2 As String
Msg1 = "There are "
Msg2 = "'s in the column"
Set Col = Range("D:D")
CntZr = Application.WorksheetFunction.CountIf(Col, 0)
If CntZr > 128 Then MsgBox Msg1 & CntZr & " 0's" & Msg2
For Cnt = 1 To 9
x = Application.WorksheetFunction.CountIf(Col, Cnt)
If x > 32 Then
MsgBox Msg1 & x & " " & Cnt & Msg2
End If
Next Cnt
End Sub
Validation with formula
Dave,
Thank you very much this should work nicely!.
Greg