tlc53
Active Member
- Joined
- Jul 26, 2018
- Messages
- 399
Hi there,
I've been struggling with this for a while now. I have found a VBA code that's close to what I want but I do not know how to amend the code so the dropdown data is on a different sheet.
On my sheet named "O2" I have a dropdown list in cells F7:F99 which currently correctly shows the dropdown description I want but I need it to return the code/value to the left of the description (0, 1, 8 & 9)
My dropdown list is located at the top left of "Sheet2" and is named "GST_Codes". It looks like this;
[TABLE="width: 200"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0 - STD[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1 - Claimable[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8 - Exempt[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9 - N/A[/TD]
[/TR]
</tbody>[/TABLE]
My poorly attempted VBA Code currently looks like this;
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20161026
selectedNa = Target.Value
If Target.Column = 5 Then
selectedNum = Application.VLookup(selectedNa, Sheet2.Range("GST_Codes"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub
I would also like the user to be able to manually enter 0, 1, 8 & 9 (ie. without selecting from the dropdown list)
Hoping a smart cookie can help me please. Thank you!!
I've been struggling with this for a while now. I have found a VBA code that's close to what I want but I do not know how to amend the code so the dropdown data is on a different sheet.
On my sheet named "O2" I have a dropdown list in cells F7:F99 which currently correctly shows the dropdown description I want but I need it to return the code/value to the left of the description (0, 1, 8 & 9)
My dropdown list is located at the top left of "Sheet2" and is named "GST_Codes". It looks like this;
[TABLE="width: 200"]
<tbody>[TR]
[TD]Code[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]0 - STD[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1 - Claimable[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]8 - Exempt[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]9 - N/A[/TD]
[/TR]
</tbody>[/TABLE]
My poorly attempted VBA Code currently looks like this;
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20161026
selectedNa = Target.Value
If Target.Column = 5 Then
selectedNum = Application.VLookup(selectedNa, Sheet2.Range("GST_Codes"), 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub
I would also like the user to be able to manually enter 0, 1, 8 & 9 (ie. without selecting from the dropdown list)
Hoping a smart cookie can help me please. Thank you!!