Is it possible to do a vlookup by using hyperlink and or hovering over a cell?

[911Kid]

I have a workbook that has over 100 + worksheets that has monthly bonus calculation request submitted (one for each employee), as well as few other sheets that has the current monthly data such as employee info like employee ID, name, department, monthly sales results, etc.. Each worksheet has a table that shows the particular employees monthly production for the department, line of business, as well as the production of the people that he oversees.. My question is regarding he employees that he oversees, listed as a 6 digit employee ID number, which could be from 0 to 30 or more, and is always included in column D starting row 2 and down (D2, D3, D4, etc..)
I want to see if there's a way to do a vlookup to a worksheet in the same workbook, by clicking on D2 which is the employee ID # and have the employees name show up in a message box , similar to the data validation message box when you get when you get a data validation error message box, and then the message box disappears when you click elsewhere, or when you move the mouse..

Thank you all in advance..

 

[Eric W]

It sounds like you're looking for a Comment in that cell. These can all be set up manually, very tedious, but you only have to do it once. Or at least only whenever you update those cells.

It would also be possible to create a VBA macro, that would detect if you select anything in that range, and if the comment doesn't exist, do the VLOOKUP and create the comment for you. If that's something you think would work, let me know and I'll write it up.

 

[911Kid]

Hi Eric,

Thank you so much for the reply. I wouldn't even attempt to put the comments as we have over 8,000 employees, and it just wouldn't be worth it...
It would be ideal to be able to activate the vlookup by either double clicking the cell with the employee ID # or even by way of having the employee ID # be a hyperlink and when the hyperlink is clicked the vlookup function displays the employee's name in a message box...

Again, I really appreciate all the help..

 

[Eric W]

I wrote up a macro that will create a comment for a cell in the range D2:D9999 if you click on the cell. You will have to put a copy of this macro in every sheet that has names in it. You said that's over 100 sheets, but I suppose that's better than 8000 names.

This macro assumes that somewhere in your spreadsheet, you have a translate table named "MyNames". It's set up like you'd expect for a VLOOKUP, employee numbers in the left column, names in the right.

Private Sub Worksheet_Change(ByVal Target As Range)
    Call Worksheet_SelectionChange(Target)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Object, EmpName As String

    If Target.Column <> 4 Or Target.Row = 1 Then Exit Sub
    
    Target.ClearComments
    EmpName = "Name not found"
    
    For Each cel In Range("MyNames")
        If cel = Target Then
            EmpName = cel.Offset(0, 1)
            Exit For
        End If
    Next cel
    Target.AddComment EmpName

End Sub

Let me know how it works for you.