Hi all,
I had a question yesterday which I got answered by AlphaFrog - (many thanks) - however, today I have another related issue I am struggling with.
As an example, I need the value for B1 to either be populated with the results of a VLOOKUP function that refers to the value of A1 and compares it to data in a table on another sheet. If it finds it in the table (in Column A) it populates with the corresponding value from column B in the table. If it does not find a match with the VLOOKUP, it has to load a defined MENU list of other choices to pick from.
This is how I am using it:
User picks a class: "MC101" in A1
In B1 I want a VLOOKUP to find MC101 in a table and return "Operational" as the cell value (since it is an operational class)
If the user picks "MC400" in A1 then the value returned for B1 after the VLOOKUP would be "Technical."
If the User enters their own class that is not found in the table, then I want them to be able to pick from a LIST in B1. (containing Operational, Technical, Certification, Other)
So if the VLOOKUP finds what it is looking for the cell is filled with an absolute value. If it does not the cell is filled with a drop down menu selection.
I got my first taste of VBA coding yesterday with this. I was wondering if I can fit any VLOOKUP code into here by replacing the Value with the formula instead of an absolute in quotes? If I leave the ELSE part of the code alone the Data Validation I have applied to the cell will probably allow for the menu to show up.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = "Other" Then
Target.Offset(, 1).Value = "ENTER CHARACTERS HERE"
Else
Target.Offset(, 1).Value = ""
End If
End If
End Sub
Any thoughts are welcome. I'm so close to finishing the functionality of this spread sheet I am working on...If I get this to work I am in great shape.
Thanks in advance,
I had a question yesterday which I got answered by AlphaFrog - (many thanks) - however, today I have another related issue I am struggling with.
As an example, I need the value for B1 to either be populated with the results of a VLOOKUP function that refers to the value of A1 and compares it to data in a table on another sheet. If it finds it in the table (in Column A) it populates with the corresponding value from column B in the table. If it does not find a match with the VLOOKUP, it has to load a defined MENU list of other choices to pick from.
This is how I am using it:
User picks a class: "MC101" in A1
In B1 I want a VLOOKUP to find MC101 in a table and return "Operational" as the cell value (since it is an operational class)
If the user picks "MC400" in A1 then the value returned for B1 after the VLOOKUP would be "Technical."
If the User enters their own class that is not found in the table, then I want them to be able to pick from a LIST in B1. (containing Operational, Technical, Certification, Other)
So if the VLOOKUP finds what it is looking for the cell is filled with an absolute value. If it does not the cell is filled with a drop down menu selection.
I got my first taste of VBA coding yesterday with this. I was wondering if I can fit any VLOOKUP code into here by replacing the Value with the formula instead of an absolute in quotes? If I leave the ELSE part of the code alone the Data Validation I have applied to the cell will probably allow for the menu to show up.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Target.Value = "Other" Then
Target.Offset(, 1).Value = "ENTER CHARACTERS HERE"
Else
Target.Offset(, 1).Value = ""
End If
End If
End Sub
Any thoughts are welcome. I'm so close to finishing the functionality of this spread sheet I am working on...If I get this to work I am in great shape.
Thanks in advance,
