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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance 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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Zeth LarssonCommented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.