Lookup for Excel Spreadsheets

I have an excel spreadsheet that I have four worksheets containing data.  I have fifth worksheet where I have copied all of the names into one column from the four spreadsheets.  I want to be able to take the values in the first column of the fifth worksheet look into the other worksheets and return the name of that worksheet. I have tried options but nothing is working can anyone assist me quickly.
Laurie KingeDiscovery ManagerAsked:
Who is Participating?
Rob HensonConnect With a Mentor Finance AnalystCommented:
With the following layout on the Summary sheet:

     A            B            C              D             E
1  Name   Sheet1   Sheet2    Sheet3    Sheet4
2  Fred      xx

Replace the Sheet1 to Sheet4 references with the relevant sheet names and then xx in cell B2 with:


If the sheet names have spaces in them then it may need a little tweak. Also assumes names are in column A on source sheets.

Then copy across and down as far as required.

Zeth LarssonConnect With a Mentor Commented:
Hope I have get right what you need.
My solution is by VBA. In the active worksheet, you doubleclick on a value in column A.
Then the procedure "Private Sub Workbook_SheetBeforeDoubleClick(ByVal sh As Object, ByVal Target As Range, Cancel As Boolean)" is executed in the workbook object, and search for the double clicked value in all worksheets in the workbook, except for the active worksheet.
In all worksheets the values to search for, must be in column A, and with no empty rows inserted.
Every worksheet where the double clicked value is found, is listed in a message box.
Paste the code in the workbook object, as in the image.
'--- paste the code in the Workbook_SheetBeforeDoubleClick procedure ------------------------------------
   Dim wb As Workbook
    Dim shs As Variant
    Dim shSearch As Worksheet
    Dim lngRow As Long
    Dim intCol As Integer
    Dim strMsg As String

    Set wb = ThisWorkbook
    Set shs = ThisWorkbook.Worksheets
    intCol = 1
    lngRow = 1
    For Each shSearch In shs
        If shSearch.Name <> sh.Name Then
            lngRow = 1
            Debug.Print shSearch.Name & vbCrLf
            Do While shSearch.Cells(lngRow, intCol).Value <> ""
                Debug.Print shSearch.Cells(lngRow, intCol).Value & vbCrLf
                If shSearch.Cells(lngRow, intCol).Value = Target.Value Then
                    strMsg = strMsg & shSearch.Name & vbCrLf
                End If
                lngRow = lngRow + 1
        End If
    Next shSearch

    Cancel = True

    If strMsg <> "" Then
        MsgBox "Value " & Target.Value & " Not found in any worksheet!", vbOKOnly, "VALUE NOT FOUND"
    End If

Hope this is something for you to build on.
Regards /Zeth
Zeth LarssonCommented:
Sample fiile.
My Excel is Swedish. If it do not work for you, tell me what error you get.
"Blad5" is source sheet. I see you must add some characters that is not found in any other sheet:-)
As a side effect, you can use the lookup function in column A in any worksheet, to look up from all other worksheets.

PS. I forgot to disable a debug.print line in the code. Second file has that line disabled.
Rob HensonFinance AnalystCommented:
No response from OP to determine which solution preferred.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.