Keyboard Shortcut to go to VLOOKUP table

[deftones]

Is there a quick way (keyboard shortcut or other method) for going to the table referenced by a vlookup? I'm a big fan of the ctrl + [ that goes to the first reference in a formula, but rather than the first cell, i want to go to the second reference. Any ideas? You're help would save me a boat load of time. Thank you.

 

[GlennUK]

Is the table in the same sheet as the VLOOKUP formula?

 

[deftones]

unfortunately it is not. why does that make a difference?

 

[GlennUK]

unfortunately it is not. why does that make a difference?

It makes a difference because if it was on the same sheet your previous shortcut would have worked ( you would have pressed tab after doing the shortcut ). Would you consider writing a macro to do this navigation for you, instead of using Excel's native features?

 

[deftones]

Yes, I would consider it, depending on the complexity. How would it look?

 

[GlennUK]

Hi, I copied a macro from Bill Manville, and added a couple of blocks where necessary ... this changed the action of the macro to halt after it has navigated to the first multicell range in the precedents of the active cell. In a normal module ( in the VBE do Insert Module ) code window paste this:

Sub GoToPrecedents()

    ' adapted from Sub FindPrecedents()
    ' written by Bill Manville
    ' With edits from PaulS
    ' adapted by Glenn Bamford - goto the first multicell precedent
    ' this procedure finds the cells which are the direct precedents of the active cell
    Dim rLast As Range, iLinkNum As Integer, iArrowNum As Integer
    Dim stMsg As String
    Dim bNewArrow As Boolean
    Application.ScreenUpdating = False
    ActiveCell.ShowPrecedents
    Set rLast = ActiveCell
    iArrowNum = 1
    iLinkNum = 1
    bNewArrow = True
    Do
        Do
            Application.Goto rLast
            On Error Resume Next
            ActiveCell.NavigateArrow TowardPrecedent:=True, ArrowNumber:=iArrowNum, LinkNumber:=iLinkNum
            If Err.Number > 0 Then Exit Do
            On Error GoTo 0
            If rLast.Address(external:=True) = ActiveCell.Address(external:=True) Then Exit Do
            bNewArrow = False
            If rLast.Worksheet.Parent.Name = ActiveCell.Worksheet.Parent.Name Then
                If rLast.Worksheet.Name = ActiveCell.Parent.Name Then
                    ' local
                    stMsg = stMsg & vbNewLine & Selection.Address
                Else
                    If Selection.Cells.Count > 1 Then ' Glenn Bamford
                        rLast.Parent.ClearArrows ' Glenn Bamford
                        Exit Sub ' drop out early, to match requirements - Glenn Bamford
                    End If  ' Glenn Bamford
                    stMsg = stMsg & vbNewLine & "'" & Selection.Parent.Name & "'!" & Selection.Address
                End If
            Else
                ' external
                    If Selection.Cells.Count > 1 Then ' Glenn Bamford
                        rLast.Parent.ClearArrows ' Glenn Bamford
                        Exit Sub ' drop out early, to match requirements - Glenn Bamford
                    End If  ' Glenn Bamford
                stMsg = stMsg & vbNewLine & Selection.Address(external:=True)
            End If
            iLinkNum = iLinkNum + 1  ' try another link
        Loop
        If bNewArrow Then Exit Do
        iLinkNum = 1
        bNewArrow = True
        iArrowNum = iArrowNum + 1  'try another arrow
    Loop
    rLast.Parent.ClearArrows
    Application.Goto rLast
    If stMsg <> "" Then MsgBox "Precedents are" & stMsg
    Exit Sub

End Sub

assign a shortcut key combination to it ( from Excel press alt-F8 and select this macro in the list, and press the Options button, and choose your shortcut key combination ).

 

[deftones]

nice, thank you for that. very efficient.

is there a way to alter to work for files that aren't open? also, what about the case where there's mutliple lookups in the same cell?

 

[GlennUK]

You could store the references in a ListBox in a userform, and then have code to open closed workbooks or navigate to the chosen reference ... unfortunately I don't have time to write that for you.