Dependant? Data Validation

Hi all

I am sure there is someone out there who knows how to do what I am hoping to achieve.

I don't know how to get a data validation (B1) to be dependant on any figure being entered into a cell to the left of it (A1).

For Example:


(A1) ##,### ¦ (B1) Drop down ¦ (C1) Calculation dependant on drop down


A1 - will have a value entered.

B1 - I would like the action of populating A1 to prompt the user to select from a list of 4 items in a dropdown list in B1, i.e. the user will not be able to continue onto next row until the selection has been made.

The drop down list will have "High" "Low" "Med" "Firm"

C1 - This will be a percentage calculation e.g. IF A1="High"*75%

High: 75%, Low: 25%, Med: 50%, Firm: 100%

I already have a working calculation formulae, I just don't know how to get the data validation (B1) to be dependant on any figure being entered into A1.

I hope my explanation is ok and that someone can help me.

Kind regards

If nothing is entered in A1, what do you want the data validation on B1 to be?

If I understand the sequence of events you want:

The user enters anything in A1

A dialog box comes up requireing the user to choose between "Low", "Mid", "High" and "Firm"

The chosen value will be put in B1

C1 will be the product of A1 with .25, .5, .75 or 1, depending on the value in B1.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim userInput As String
If Target.Address = "$A$1" Then
    If IsNumeric(Target.Value) Then
    Do
    userInput = Application.InputBox("Low, Mid, High, or Firm", Default:="Mid", Type:=2)
    If userInput = "False" Then
        Rem Cancel pressed
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        Exit Sub
    End If
    Loop Until userInput Like "[LMHFlmhf]*"
    Application.EnableEvents = False
    Range("b1").Value = Application.Proper(userInput)

    Select Case UCase(Left(userInput, 1))
        Case "L"
            Range("c1") = Val(Range("a1").Value) * 0.25
        Case "M"
            Range("c1") = Val(Range("a1").Value) * 0.5
        Case "H"
            Range("c1") = Val(Range("a1").Value) * 0.75
        Case "F"
            Range("c1") = Val(Range("a1").Value)
    End Select
    Application.EnableEvents = True
    End If
End If

End Sub

Hello mikerickson

Thank you for your quick reply.

If nothing is entered into A1 then nothing should happen. The purpose of this is to make sure that if a user enters any figure in A1 they are forced to make a selection from B1 so that C1 can be calculated.

I am not that advanced in my knowledge of excel and don't know how to implement the code that you have written.

I would really appreacicate your help by telling me if this is a macro, function or data validation.

I have attached a zipped file of a portion of the spreadsheet I am creating for you to see what I have done.

Many thanks

Sorry for posting the tread twice. I thought the first one I posted was in the wrong place and I couldn't find it after I posted it.