Display message in a cell based on value in a cell that is selected

[minisp4m]

Hello!

I have a table with codes E4:P250, example of codes mjk502, mjb333 etc. I would like, if possible, that if I select any cell, for example F65, that has the code mjk502, it displays what the code means in C2. All codes are in a different sheet in the same workbook.

 

[Eric W]

You can't do that with native Excel functions, since there's no way to know which cell is active. But you can do it with VBA.

Open a COPY of your workbook to the sheet with the codes range E4:P250. Right click on the sheet tab on the bottom and select View Code. Paste the following code into the window that opens:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    [C2].ClearContents
    If Not Intersect(Target, Range("E4:P250")) Is Nothing Then
        On Error Resume Next
        [C2].Value = WorksheetFunction.VLookup(Target.Value, Sheets("Sheet7").Range("A1:B2"), 2, 0)
    End If
End Sub

The values in red refer to the locations on the current sheet. The values in blue are where the codes and meanings are defined in the other sheet, codes in column A, meanings in column B. Change these values as needed. Then close the VBA editor (Alt-Q or the X in the upper right). You should now get the meaning in C4 as you desire.

 

[minisp4m]

You can't do that with native Excel functions, since there's no way to know which cell is active. But you can do it with VBA.

Open a COPY of your workbook to the sheet with the codes range E4:P250. Right click on the sheet tab on the bottom and select View Code. Paste the following code into the window that opens:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    [C2].ClearContents
    If Not Intersect(Target, Range("E4:P250")) Is Nothing Then
        On Error Resume Next
        [C2].Value = WorksheetFunction.VLookup(Target.Value, Sheets("Sheet7").Range("A1:B2"), 2, 0)
    End If
End Sub

The values in red refer to the locations on the current sheet. The values in blue are where the codes and meanings are defined in the other sheet, codes in column A, meanings in column B. Change these values as needed. Then close the VBA editor (Alt-Q or the X in the upper right). You should now get the meaning in C4 as you desire.

Thank you!!

 

[Eric W]

You're welcome!