Hi there,
I'm very new to the VBA, and have a reasonable Excel knowledge of formulas etc.
I have a sheet that I use an Array Formula to give me 4 options which is referenced in a range by a data validation drop down list (each row has it's own range & result.).
The Vlookup is based on the value in col D, (searches on sheet 'Fixture Library' and result is produced in cols N-Q, there will only be a maximum of 4 results. The data validation references the N-Q range for each row. This all works well.
The next step is what I'm struggling with - I have found a great VBA script online that on clicking on the data validation cell will refresh the result to the first. I would like instead of clicking on the cell with the validation that anytime there is a change in col D, it refreshes it's result in col F. Is this possible can anyone help with the script?
Example file is here: Dan Young sent you 1 item. - A number of cols are hidden/deleted to prevent confusion.
Script I am currently using:
I'm very new to the VBA, and have a reasonable Excel knowledge of formulas etc.
I have a sheet that I use an Array Formula to give me 4 options which is referenced in a range by a data validation drop down list (each row has it's own range & result.).
The Vlookup is based on the value in col D, (searches on sheet 'Fixture Library' and result is produced in cols N-Q, there will only be a maximum of 4 results. The data validation references the N-Q range for each row. This all works well.
The next step is what I'm struggling with - I have found a great VBA script online that on clicking on the data validation cell will refresh the result to the first. I would like instead of clicking on the cell with the validation that anytime there is a change in col D, it refreshes it's result in col F. Is this possible can anyone help with the script?
Example file is here: Dan Young sent you 1 item. - A number of cols are hidden/deleted to prevent confusion.
Script I am currently using:
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Updateby Extendoffice 20160725
Dim xFormula As String
On Error GoTo Out:
xFormula = Target.Cells(1).Validation.Formula1
If Left(xFormula, 1) = "=" Then
Target.Cells(1) = Range(Mid(xFormula, 1)).Cells(1).Value
End If
Out:
End Sub
••••ˇˇˇˇ