Hi all

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

I am creating a spreadsheet for the staff to enter amounts into each month. I have attached spreadsheet file for you to see.

I would like help to achieve the following:

When a user enters an amount in a cell in the columns with the months above it (i.e. Rows 7 onwards and columns C, F & I etc), I want to force the user to select either “High”, “Med”, “Low” or “Firm” from the cell to the right of the cell they are populating, before they can continue.

For the user to select the High, Low, Med & Firm drop down cells I used the Data Validation List function.

I have set the grey cells with a formula that recognises the selected text and uses it to calculate a value from the user entered figure. Here is the formula I have used:

=IF(E7="High",D7*75%)+IF(E7="Med",D7*50%)+IF(E7="L ow",D7*25%)+IF(E7="Firm",D7*100%)

I hope someone is able to help with this.

Kind regards

Howdy, and welcome to the board.

One suggestion, not directly related to your request, but to design to help you with your request. Normally (in western languages), people move from right to left, not the other way. It seems more logical to have the required cell prior to what you desire. Thus, change columns C and D and other pairs of columns).

Also, it seems that VBA code in a private module (worksheet event) would be the best approach.

Hello Shades and thank you for your kind welcome.

I move from left to right in my language too, but I am working to some very specific requests here.

Any idea on how I might be able to acheive this using VBA code in a private module?

put the following code inside sheet1


Dim a As String
Private Sub Worksheet_Change(ByVal Target As Range)

If IsEmpty(Cells(Target.Row, "C")) Then
Exit Sub
Else
Range("D" & Target.Row).Select
If Cells(Target.Row, "D") = "Select" Then
a = Target.Row
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not a = "" Then
If Cells(a, "D") = "Select" Then
MsgBox ("please select")
Range("D" & a).Select
End If
End If
End Sub


I think it would help u