data validation

[sjoerd.bosch]

I need a data validation restriction for a cell
problem is that the cell in which I need the restriction contains a formula and I guess that is the reason why my data validation entry does not work.
For example: cell d4 / cell d5 = result in cell d6 The result in cell d6 should be not less than 12.0000 (decimals must be allowed)
A VBA code is also possible, but I thought this would be something simple in data validation

 

[BigBeachBananas]

I need a data validation restriction for a cell
problem is that the cell in which I need the restriction contains a formula and I guess that is the reason why my data validation entry does not work.
For example: cell d4 / cell d5 = result in cell d6 The result in cell d6 should be not less than 12.0000 (decimals must be allowed)
A VBA code is also possible, but I thought this would be something simple in data validation

You can only validate on cell input, but formula output.

If you're looking to cap the minimum, you can try

=MAX(12,D4/D5)

This will always give you the lowest value of 12.

 

[sjoerd.bosch]

This is not working for me. Will try to explain more detailed
D4: 272 [is the input cell]
D5: 24 [is also an input cell]
D6: 11.3 [is the cell where the formula =D4/D5 is and which I need to restrict to MIN 12.0000]
If I enter the data validation in Cell D4 - =MIN(12,D4/D5) - it is not working

 

[BigBeachBananas]

This is not working for me. Will try to explain more detailed
D4: 272 [is the input cell]
D5: 24 [is also an input cell]
D6: 11.3 [is the cell where the formula =D4/D5 is and which I need to restrict to MIN 12.0000]
If I enter the data validation in Cell D4 - =MIN(12,D4/D5) - it is not working

In Cell D6, insert

=MAX(12,D4/D5)

 

[sjoerd.bosch]

Inserted

In Cell D6, insert

=MAX(12,D4/D5)

You can only validate on cell input, but formula output.

If you're looking to cap the minimum, you can try

=MAX(12,D4/D5)

This will always give you the lowest value of 12.

I am not trying to cap the value - I just need a warning message to come up
other value must be allowed in special cases

So I would like cell D6 to display a message if the value is less than 12
and neither of the formulas work for me btw

 

[kevin9999]

Another option is a worksheet change event. First, change your formula to:

=IFERROR(D4/D5,0)

And put this code on the sheet code area of the sheet with the formula:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D4:D5"), Target) Is Nothing Then
    On Error GoTo Escape
    Application.EnableEvents = False
        If Range("D6") < 12 Then
            MsgBox "Formula results in D5 less than 12 - please re-enter"
            Target.ClearContents
            Target.Select
        End If
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[sjoerd.bosch]

apologies - the

Inserted



I am not trying to cap the value - I just need a warning message to come up
other value must be allowed in special cases

So I would like cell D6 to display a message if the value is less than 12
and neither of the formulas work for me btw

max formula does work, but not looking to max the value. Just a warning message from data validation

 

[BigBeachBananas]

apologies - the

max formula does work, but not looking to max the value. Just a warning message from data validation

How about

=IF(D4/D5<12, "The current value is " & D4/D5 & " which is less than 12", D4/D5)

 

[bebo021999]

Yes, data validation will not work for cell with formula
But for warning purpose only, why not using conditioning formating in cell D6?
=D6<12

 

[sjoerd.bosch]

I have d

Yes, data validation will not work for cell with formula
But for warning purpose only, why not using conditioning formating in cell D6?
=D6<12

View attachment 99972

Done that - but I prefer to display a message to the users, as they just ignore the colour change